DBCC PAGE Command to Analyze SQL Server Database Objects

0

One of the most underrated commands, one can find in SQL is DBCC PAGE command. We are saying this not because, the command is not worthy enough to be used, but because it is an undocumented internal command of SQL Server. You will not be able to find much info about this command in online books as well. Therefore, due to lack of information, most SQL users do not use this command.

In this write-up, we will enlighten the users about the use of DBCC PAGE command and the process to analyze SQL Server Table and index data with this command.

DBCC PAGE SYNTAX

As discussed earlier, since DBCC PAGE is an undocumented command, if the users want to get the results of this command in their query output window, traceflag 3604 needs to be turned on.

Syntax of DBCC PAGE is:

DBCC traceon (3604)
DBCC page (‘Aria’, dbid), filenum, pagenum [, printopt={0|1|2|3}])
Go

The parameters used in the syntax and their functions are:

Parameter Description
dbid ID of the database which contains the database
dbname Name of the database which contains the page
filenum File number which contains the page
pagenum Page number of the file which contains the page
printopt Optional print option

The printopt has different parameters, which have following meaning:

  • O-used for printing only the page header
  • 1- used for printing page header, per-row hex dumps and page slot array dump
  • 2-used for printing page header and whole page dump
  • 3-used for printing header and detailed per-row information

Analyzing DBCC PAGE Command Results

The output obtained after the DBCC PAGE command is run, is divided into 4 sections-

  • Buffer: The buffer section displays the information about the buffer that manages a page.
  • Page Header
  • Data
  • Offset Table

DBCC PAGE displays the data in rows and in the group of 4 bytes at a particular time. Within every group, the bytes are arranged in a reverse order. Therefore, the first group will be something like byte 3, byte 2, byte 1, and byte 0.

For accessing the rows of data by using the DBCC PAGE command, the users require a page address for the data page. This can be done by taking a value from the column that is called from the sysindexes table.

If a row of a table has a value 0, then it means that the table is a heap and if the value is 1, then the table possesses a clustered index.

For example, the query will be like:

SELECT first FROM sysindexes

The output of the above query will be a hexadecimal value, which when converted will give the file and the page address. It is to be noted that SQL Server 7.0 does not guarantee that the first column of sysindexes will identify the first page of the table in an accurate manner.

Conclusion

The DBCC PAGE is an internal command in SQL Server that is not documented. It enables the users to analyze and know about the table and index data of SQL Server database in an effectual manner.

Andrew Jackson

I am SQL DBA and SQL Server blogger too. I like to share about SQL Server and the problems related to it as well as their solution and also I do handle database related user queries, server or database maintenance, database management etc.

Facebook Twitter LinkedIn Google+ 

%d bloggers like this: