Sql-server – Index Pages (page type 2)

database-internalsindexpage-splitssql server

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?

Best Answer

Index pages (type 2) hold the non-leaf level(s) of the clustered index b-tree. The leaf level of the clustered index is just the underlying object's data pages themselves.

In the special case where the whole object (table in your case) fits in a single page, SQL Server does not create a separate index page, because there is no need.

In your example, the first index page is created when the page split results in the table consisting of more than one data page for the first time.

Index pages are also used for all levels of nonclustered b-tree indexes.

Index pages can also be inspected with DBCC PAGE. Dump style 3 is the most informative, as it shows the child file and page pointers for index key ranges. This is the information needed to navigate down (potentially multiple levels of) the b-tree index.

As the number of rows in the index increases, the number of levels in the index will also increase.

See:

Comprehensive information about table and index internals can be found in the Microsoft SQL Server Internals books by Kalen Delaney et al.

A new index page will be created whenever an existing index page needs to split. If that page was the root page (top of the index tree), the index gains a new level as a result. Index pages can split if the index key is widened, or if another entry is needed on that page (e.g. to refer to a new child page) and there isn't enough room.

Related Question