September 4, 2013 at 4:37 pm
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.
September 4, 2013 at 4:56 pm
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
September 4, 2013 at 5:47 pm
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 ?
September 4, 2013 at 5:52 pm
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
September 5, 2013 at 10:35 am
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 ?
September 5, 2013 at 10:41 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply