Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Clustered indexes Expand / Collapse
Author
Message
Posted Wednesday, September 4, 2013 4:37 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 5:09 PM
Points: 178, Visits: 579
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.
Post #1491576
Posted Wednesday, September 4, 2013 4:56 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 42,818, Visits: 35,943
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 2008, MVP
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

Post #1491579
Posted Wednesday, September 4, 2013 5:47 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 5:09 PM
Points: 178, Visits: 579
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 ?
Post #1491587
Posted Wednesday, September 4, 2013 5:52 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 42,818, Visits: 35,943
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 2008, MVP
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

Post #1491588
Posted Thursday, September 5, 2013 10:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 5:09 PM
Points: 178, Visits: 579
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 ?
Post #1491883
Posted Thursday, September 5, 2013 10:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 42,818, Visits: 35,943
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 2008, MVP
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

Post #1491884
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse