SQL Server use 8K page(8192 Bytes). Only 8060 bytes can be used to store the row.
Each table can either have Clustered index page or Heap page.
Clustered index page id is 1. Heap Page id is 0.
DBCC IND( ‘DB NAME’,’TABLE NAME’,OPTION)
The option can be one of the following
- non-clustered Index ID
- 1 = Clustered Index ID
- 0 = in-row data pages and in-row IAM pages (from Heap)
- -1 = all pages of all indexes including LOB (Large object binary) pages and row-overflow pages
- -2 = all IAM pages
IAM INFO is NULL meaning it is the IAM page. Page type 10 also means it is IAM page.
- 1 : data page
- 2 : index page
- 3 ,4 - text pages
- 8 : GAM page
- 9 : SGAM page
- 10 : IAM page
- 11 :PFS page
To exam detail what is inside of the page. Use DBCC PAGE
DBCC PAGE(‘DB NAME| DB ID’, FILE NUMBER,PAGE NUMBER, PRINT OPTION)
There are 4 print options:
- 0:Just page header
- 1: Page header + per row Hex dump + slot array
- 2: Page Header + Whole page Hex dump
- 3: Page Header + Per row data
Option 3 is the most human readable format. DBCC trace on 3604 is for output the information to the console instead of writing in the error log.
Inside the Storage Engine: Using DBCC PAGE and DBCC IND to find out if page splits ever roll back http://www.sqlskills.com/blogs/paul/post/Inside-the-Storage-Engine-Using-DBCC-PAGE-and-DBCC-IND-to-find-out-if-page-splits-ever-roll-back.aspx