Two questions: 1, (clustered or nonclustered index page will appear) also split;

The two problem:
1, (clustered or nonclustered index page) will not be a page split; 2, non clustered index storage when there is no sort
How to test and verify, thank you

Science does not distinguish unknown











Happy Mid Autumn Festival, national day, may you be happy and prosperous!, advertising away

Started by Stanford at February 14, 2016 - 11:29 AM

A nonclustered index is not in accordance with the physical storage index

Posted by Helen at February 26, 2016 - 12:23 PM

1, (clustered or nonclustered index page) will not be a page split; 2, non clustered index storage when there is no sort

1, Will split, each split moving half of the data to a new page.
2, No order, but the leaf node will store the clustered index key value.

Posted by Darren at February 28, 2016 - 12:48 PM

The implementation of the two script, have a look inside the analytical results, can look for Books Online
/*
Create a table, storing the data
*/
USE AdventureWorks2012
GO
CREATE TABLE dbo.ClusteredPageSplits
(
  RowId INT IDENTITY(1,1)
  ,FillerData VARCHAR(2500)
  ,CONSTRAINT PK_ClusteredPageSplits PRIMARY KEY CLUSTERED (RowId)
);
INSERT INTO dbo.ClusteredPageSplits (FillerData)
SELECT TOP 24 REPLICATE('X',2000)
FROM sys.objects;
DECLARE @ObjectID INT = OBJECT_ID('dbo.ClusteredPageSplits');
SELECT object_id, index_type_desc, index_level, page_count, record_count
FROM sys.dm_db_index_physical_stats (DB_ID(), @ObjectID, NULL, NULL, 'DETAILED');
GO

/*
The data line is increased, so that it can not let Naixin data paging,
*/
USE AdventureWorks2012
GO
CREATE TABLE dbo.ClusteredPageSplits
(
  RowId INT IDENTITY(1,1)
  ,FillerData VARCHAR(2500)
  ,CONSTRAINT PK_ClusteredPageSplits PRIMARY KEY CLUSTERED (RowId)
);
INSERT INTO dbo.ClusteredPageSplits (FillerData)
SELECT TOP 24 REPLICATE('X',2000)
FROM sys.objects;
DECLARE @ObjectID INT = OBJECT_ID('dbo.ClusteredPageSplits');
SELECT object_id, index_type_desc, index_level, page_count, record_count
FROM sys.dm_db_index_physical_stats (DB_ID(), @ObjectID, NULL, NULL, 'DETAILED');
GO

Posted by Darren at March 07, 2016 - 1:34 PM

This looks so familiar?

Posted by Rachel at March 17, 2016 - 2:24 PM

expert_performance_indexing_for_sql_server_2012 This example of the book

Posted by Darren at March 30, 2016 - 3:14 PM

1, (clustered or nonclustered index page) will not be a page split,
Will split, each split moving half of the data to a new page.


No order, there will be a page split, so a page split what?

Posted by Stanford at March 31, 2016 - 3:20 PM

I said that the index page, page filled with resolution.

Posted by Darren at April 06, 2016 - 4:10 PM

Posted by Stanford at April 11, 2016 - 4:44 PM

Is also a non clustered index, because the index column increment type are not the same, which causes great difference in avg_page_space_used_in_percent
My understanding is, non aggregated in index row is stored sequentially, causing a page split non clustered index row in the B tree, so that the avg_page_space_used_in_percent difference.

Posted by Stanford at April 13, 2016 - 5:36 PM

To leave a name look, you can see the page content Page determine whether sorting, also pay attention to the fill factor

Posted by Andy at April 16, 2016 - 6:26 PM

A clustered index page splitting I understand

Posted by Stanford at April 25, 2016 - 6:43 PM

Hello, on avg_fragmentation_in_percent,
Then the list of non clustered index when avg_fragmentation_in_percent is close to 100%, that is to say no consecutive page basically
Since the additional non clustered index, avg_fragmentation_in_percent only 10%

Posted by Stanford at May 03, 2016 - 6:49 PM

Have a look first,
Learning to learn

Posted by Adam at May 04, 2016 - 7:17 PM

Have a look first
It should be useful

Posted by Adam at May 07, 2016 - 7:56 PM

My understanding is, non aggregated in index row is stored sequentially, causing a page split non clustered index row in the B tree, so that the avg_page_space_used_in_percent difference.

The index is ordered. Non aggregation can be understood and then mapped to find data on a clustered index for the first lookup index column

Posted by Rachel at May 09, 2016 - 7:59 PM

No clustered index table called heap table, even if it is a non clustered index. Your chart also shows that, the leaf nodes of a non clustered index points to the clustered index key values or heap table RID. Avg_fragmentation_in_percent is the size of the fragments, according to my understanding: physical results fragment is the index page splitting, fragmentation occurred in the index page no longer is the physical order, one page only 8K, if you insert a data, but the data size is not the same, page use rate of course is different, your GUID and INT contrast is not reasonable.

Posted by Darren at May 21, 2016 - 8:49 PM

avg_page_space_used_in_percent 100% , 10%?

Posted by Rachel at June 04, 2016 - 9:41 PM

Do not say first clustered index, said reactor is built on the table a nonclustered index, then simplifies the said, on the construction of unique nonclustered index
I know GUID storage space than int
The number I didn't go to contrast index rows occupied page, with the percentage of fragmentation percentage I only see page,
Here the index page using the percentage difference between the percentage of how to explain and debris?

Posted by Stanford at June 08, 2016 - 10:02 PM

I will not screenshots
On the script, a matter of a few seconds, their own testing at

if exists(select 1 from sys.objects where name='TestNoClusteredIndexSort')
drop table TestNoClusteredIndexSort

create table TestNoClusteredIndexSort
(
	id int identity(1,1),
	Name int,
	col1 uniqueidentifier,
	remark varchar(50)
);

create index index_name on TestNoClusteredIndexSort(name);


create index index_col1 on TestNoClusteredIndexSort(col1);

declare @i int
set @i=1;
while @i<=10000
begin
	insert into TestNoClusteredIndexSort values(@i,NEWID(),NEWID())
	set @i=@i+1
end

Posted by Stanford at June 18, 2016 - 10:55 PM

Avg_page_space_used_in_percent is

96.6652705707932 int
Yes
59.7567333827527 GUID

Avg_fragmentation_in_percent is
17.3913043478261 int
Yes
93.5483870967742 GUID

Posted by Stanford at June 21, 2016 - 11:24 PM

You can see, NEWID () is a disorder of the hexadecimal number sixteen, a new GUID in any location may enter the index.
If the index to 500 consecutive keys to a page, then 500 new data may be distributed in the 499 page, because its value is not continuous.
And if it is self added, because most keys are in the same index page, so the number of index pages will become less, the utilization rate will be high.

Posted by Andy at July 02, 2016 - 11:39 PM

The duck analysis correctly.

Posted by Darren at July 11, 2016 - 11:44 PM

This can explain the nonclustered cable storage is of the order of the index line in it?

My understanding is a non clustered index keys are stored sequentially stored,
If the 100 page index row, each page is full, the most ideal situation
Generate a random key, because the stored sequentially, is stored in a leaf index row, resulting in a page split,
Then generates a random key, in order to store, he according to the order of storage, storage in another page index row, most likely caused by a page split
...
That is to say, in a utilization rate of close to 100% pages, because random clustered key, at any time may be split
On the whole, storage index row page utilization rate is about 50% in the

The order of growth of ID as a non clustered index, the front page of utilization is full, not behind the index key values generated to interrupt previously stored full page
On the whole, storage index row page utilization ratio close to the ideal situation, is 100%

Posted by Stanford at July 13, 2016 - 12:36 AM

Because of the randomness of non clustered key, at any time may be split

Posted by Stanford at November 14, 2016 - 6:39 AM

1 clustered index will split, non clustered index said not sorted, why split?

Posted by Paddy at November 17, 2016 - 6:53 AM

This depends on the specific circumstances, GUID is generally used as a non clustered index, if it is used as a clustered index key words do you say the situation. Since the increasing while the utilization rate is high, but should also take into account the extremum problem.

In general, the hard disk is not missing, so the seriousness of the problem is not big, the index page dispersion degree of influence on search is not too big, so I do not think about. . . .

Posted by Andy at November 21, 2016 - 7:27 AM

Problem a: will split, but each split moving half of the data to a new page.
Question two: no order, but the leaf node will store the clustered index key value.

Posted by Lyle at December 03, 2016 - 7:40 AM

Ask a nonclustered index storage why no sort?

Posted by Sid at December 06, 2016 - 7:56 AM

So many reply, why not question?

Posted by Sid at December 15, 2016 - 8:31 AM

Seemed to say, did not speak with the test data of fact,
96.6652705707932 int
Yes
59.7567333827527 GUID

Avg_fragmentation_in_percent is
17.3913043478261 int
Yes
93.5483870967742 GUID

The causes of these data, there is no reasonable explanation

Posted by Stanford at December 27, 2016 - 9:12 AM

I think again, seem to be not, if "pages full on the resolution", each page has a full time, where each page has been split time
With the continuous separation, the index page filling rate is at 50%.
But this is inconsistent with the facts.?

I do not make trouble out of nothing, just some questions to the general, headacheļ¼
I have read "sqlserver2008" in-depth analysis, then look up the data behind

Posted by Stanford at December 29, 2016 - 9:54 AM

Posted by Stanford at January 02, 2017 - 10:45 AM

So many reply, why not question?

Posted by Candice at January 07, 2017 - 10:40 AM

Very useful, leave a name first~

Posted by Nan at January 15, 2017 - 11:22 AM