Index Pages (page type 2)

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 = repl

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:

• Clustered Index Structures

• Nonclustered Index Structures

• Heap Structures

• Table and Index Organization

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.

StackExchange Database Administrators Q#159239, answer score: 14