Sunday, November 6, 2011

0 Something about the page

SQL Server data page with row offsets

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

Example:

  1. DBCC IND ('AdventureWorks','dbo.AWBuildVersion',1)

image

IAM INFO is NULL meaning it is the IAM page. Page type 10 also means it is IAM page.

Page type:

  • 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
  1. DBCC TRACEON(3604);
  2. DBCC PAGE ('AdventureWorks',1,719,0)

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.

 

Reference

http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocDBCC.htm

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

http://msdn.microsoft.com/en-us/library/cc280360.aspx

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates