I was writing another article on the smallest dataset possible in sql server, and i came across a situation where I need to use DBCC PAGE () statement for a table. DBCC PAGE () statement, uses database name, file number, page number, and print Option as arguments. In my case, i needed to find the page number of the table in order for me to use this statement. Then I came across the undocumented DBCC IND() statement which lists the ‘data’ and ‘index’ pages of the table.
For the test purpose, I created a database , and a table , when I ran DBCC IND() statement against it, it returned no rows.
This was the expected behavior, since the table was empty, sql server engine hasn’t allocated any pages for it.
In the next step, i insert a row into the table to see how many pages it has allocated.
What does all this data mean? For purposes of this tip, we'll focus on a couple of key columns. The columns PageFID and PagePID represent a file number where pages reside and a page number within the file where data lives, respectively. IndexID is the index' index_id as found in sys.indexes. PageType dictates the kind of page. Type = 1 is a data page, Type = 2 is an index page, and Type = 10 is the IAM page that maintains the index itself. IndexLevel is the level within the IAM structure the page falls. If level = 0, then this is a leaf level page for the index. For more detailed information on all the columns (this is an undocumented command after all), this MSDN blog by former Microsoft storage engine expert Paul Randal breaks them all down.
I hope this help you in some manner. please let me know your thoughts and comments.
Aneesh
Post a Comment
Post a Comment