I trying to understand page splits in SQL Server, reading WHAT IS A PAGE SPLIT? WHAT HAPPENS? WHY DOES IT HAPPEN? WHY WORRY? by Tony Rogerson
CREATE TABLE mytest
(
something_to_see_in_data CHAR(5) NOT NULL CONSTRAINT pk_mytest PRIMARY KEY CLUSTERED,
filler VARCHAR(3000) NOT NULL
)
go
insert mytest ( something_to_see_in_data, filler ) values( '00001', replicate( 'A', 3000 ) )
insert mytest ( something_to_see_in_data, filler ) values( '00002', replicate( 'B', 1000 ) )
insert mytest ( something_to_see_in_data, filler ) values( '00003', replicate( 'C', 3000 ) )
go
To check the pages of my table:
DBCC IND ( 0, 'mytest', 1);
+---------+---------+--------+--------+------------+---------+-----------------+-------------------+----------------+----------+------------+
| PageFID | PagePID | IAMFID | IAMPID | ObjectID | IndexID | PartitionNumber | PartitionID | iam_chain_type | PageType | IndexLevel |
+---------+---------+--------+--------+------------+---------+-----------------+-------------------+----------------+----------+------------+
| 1 | 3520 | NULL | NULL | 2065259704 | 1 | 1 | 72057595357560832 | In-row data | 10 | NULL |
| 1 | 3519 | 1 | 3520 | 2065259704 | 1 | 1 | 72057595357560832 | In-row data | 1 | 0 |
+---------+---------+--------+--------+------------+---------+-----------------+-------------------+----------------+----------+------------+
To check the page details of data page:
dbcc traceon( 3604 )
go
DBCC page( 0, 1, 3519, 1 ) with tableresults
Offset:
Slot 0, Offset 0x60, Length 3016, DumpStyle BYTE
Slot 1, Offset 0xc28, Length 1016, DumpStyle BYTE
Slot 2, Offset 0x1020, Length 3016, DumpStyle BYTE
Updating one of the records, so that it will not fit into the current page, and page split will happen (i.e.) new page will be created?
update mytest
set filler = replicate( 'B', 3000 )
where something_to_see_in_data = '00002'
Now checking the pages again:
DBCC IND ( 0, 'mytest', 1);
+---------+---------+--------+--------+------------+---------+-----------------+-------------------+----------------+----------+------------+
| PageFID | PagePID | IAMFID | IAMPID | ObjectID | IndexID | PartitionNumber | PartitionID | iam_chain_type | PageType | IndexLevel |
+---------+---------+--------+--------+------------+---------+-----------------+-------------------+----------------+----------+------------+
| 1 | 3520 | NULL | NULL | 2065259704 | 1 | 1 | 72057595357560832 | In-row data | 10 | NULL |
| 1 | 3519 | 1 | 3520 | 2065259704 | 1 | 1 | 72057595357560832 | In-row data | 1 | 0 |
| 1 | 3521 | 1 | 3520 | 2065259704 | 1 | 1 | 72057595357560832 | In-row data | 2 | 1 |
| 1 | 3522 | 1 | 3520 | 2065259704 | 1 | 1 | 72057595357560832 | In-row data | 1 | 0 |
+---------+---------+--------+--------+------------+---------+-----------------+-------------------+----------------+----------+------------+
As we can see, two new pages got created:
3521 -- Index page
3522 -- Data page
I can understand the reason behind creation of new Data page(3522)
because my data is more than 8kb so new page got created.
What is the use of the Index page and when will it will be created? I did lot of research in Google there is no proper documentation on Index page. Is it for maintaining B-Tree?