SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Clustered indexes


Clustered indexes

Author
Message
sqlnewbie17
sqlnewbie17
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1743 Visits: 1346
Clustered indexes also have some disadvantages compared to heaps. When you insert a new row into a full page, SQL Server has to split the page into two pages and move half of the rows to the second page. This happens because SQL Server needs to maintain the logical order of the rows. This way, you get some internal fragmentation, which you cannot get in a heap. In addition, the new page (or new uniform extent for a large table) can be reserved anywhere in a data file.

It says "When you insert a new row into a full page, SQL Server has to split the page into two pages and move half of the rows to the second page"
Adding a new extent/page is correct when the given space is completely full or does not accomodate the new data to be added but what does it mean when it says "SQL Server has to split the page into two pages and move half of the rows to the second page"

1.TRUNCATE TABLE dbo.TestStructure;
2.CREATE CLUSTERED INDEX idx_cl_id ON dbo.TestStructure(id);
3.
DECLARE @i AS int = 0;
WHILE @i < 18630
BEGIN
SET @i = @i + 1;
INSERT INTO dbo.TestStructure
(id, filler1, filler2)
VALUES
(@i, 'a', 'b');
END;
4.SELECT index_type_desc, index_depth, index_level, page_count,
record_count, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID(N'tempdb'), OBJECT_ID(N'dbo.TestStructure', N'U'), NULL, NULL , 'DETAILED');
The result :
index_type_desc index_depth index_level page_count record_count avg_pg_spc_used_in_pct
--------------- ----------- ----------- ---------- ------------ ----------------------
CLUSTERED INDEX 2 0 621 18630 98.1961947121324
CLUSTERED INDEX 2 1 1 621 99.7158388930072
5.INSERT INTO dbo.TestStructure(id, filler1, filler2)VALUES
(18631, 'a', 'b');
6.Run code in step 4:result is:
index_type_desc index_depth index_level page_count record_count avg_pg_spc_used_in_pct
--------------- ----------- ----------- ---------- ------------ ----------------------
CLUSTERED INDEX 3 0 622 18631 98.0435507783543
CLUSTERED INDEX 3 1 2 622 49.9258710155671
CLUSTERED INDEX 3 2 1 2 0.296515937731653

And the explanation says:
Now the index has three levels. Because a new page was allocated on the leaf level, the original root page could not reference all leaf pages anymore. SQL Server added an intermediate level with two pages pointing to 622 leaf pages, and a new root page pointing to the two intermediate-level pages.
Why was a new page allocated here ?I have not understood the concept entirely.can some please explain this.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)

Group: General Forum Members
Points: 232507 Visits: 46360
You tried to insert into a page that did not have enough space. Can't fit. So SQL allocates a new page, moves half (or other fraction) of the rows on the full page to the new page and links the new page into the index chain. That's a page split.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


sqlnewbie17
sqlnewbie17
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1743 Visits: 1346
Does the data move to a new leaf level whenever a new extent is created to insert new data which does not fit into the existing extent/page?
Lets say there are 2 index levels (0 and 1)
1 contains the data
If level 2 is now created (0,1 and 2)what happens to the data in level 1.Is all the data in level 1 moved to level 2 ?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)

Group: General Forum Members
Points: 232507 Visits: 46360
No such thing as a new leaf level. Levels are created when the root needs to split and a new root added above that.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


sqlnewbie17
sqlnewbie17
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1743 Visits: 1346
So when the new root is created it contains 2 pointers to the split up old root(s).Is that right?So each time a new level is added the old root is split and a the new root contains pointer to the old root(s)?Is that right ?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)

Group: General Forum Members
Points: 232507 Visits: 46360
Root page full, root page splits into two (old root page and newly added page). Cannot be two root pages, to another level is added above with a single page, that becomes the new root page. Behaves exactly like any other non-leaf level of an index.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search