DBCC PAGE to Display Contents of Data Pages in SQL Server
DBCC PAGE command is used to display contents of data pages where table rows data are stored in SQL Server database tables. Database administrators and SQL developers can use DBCC PAGE statement for displaying data in certain data page.
In this SQL Server tutorial, I'll try to summarize how SQL professionals can use DBCC TRACEON, DBCC IND and DBCC PAGE commands to see structure of a data page and show its contents with sample codes.
First of all, let's create a new database table and populate sql table with sample data.
CREATE TABLE DataPages (
customerid int identity(1,1) not null primary key,
company nvarchar(100),
url varchar(1000),
firstname nvarchar(50) not null,
middlename nvarchar(50),
lastname nvarchar(50) not null,
address nvarchar(1000),
nationality char(2),
age smallint,
insertdate datetime
)
GO
INSERT INTO DataPages
SELECT N'Kodyaz', 'http://www.kodyaz.com', N'Eralper', NULL, N'Yılmaz', N'Şişli İstanbul, Turkey','TR',40,Getdate()
Now we can expect to see inserted values in SQL Server database data pages using DBCC commands as shown in the following sections of this tutorial
Just to remember, a data page has fixed size of 8 KB.
A data page is formed of three sections:
Page Header: 96 bytes,
Pay load where data itself is stored,
Row Offset Array: 2 byte pointers to rows in current data page payload area.
DBCC PAGE statement prints out the above summarized contents of a specific SQL data page.
SQL administrators can use below syntax for DBCC PAGE command.
dbcc page ( {dbid | 'dbname'}, filenum, pagenum [, print option={0|1|2|3} ])
STEP 1)
But before executing dbcc page statement, administrators have to enable the required specified trace flags using "DBCC TRACEON()" command with 3604 trace flag.
For supported trace flags, please refer to TechNet article. Unfortunately trage flag 3604 is not listed on this page.
Any how, running SQL command " DBCC TRACEON(3604) " enables the output of the following DBCC commands to be redirected to the executing client which is in out case the SSMS (SQL Server Management Studio). So we can see the page details using SQL Server Management Studio query window.
DBCC TRACEON(3604)
-- DBCC execution completed. If DBCC printed error messages, contact your system administrator.
By the way, I'm running this demo on a in-memory optimized database on SQL Server 2014 CTP2, to see whether DBCC PAGE command will help developers to see data page contents of a memory optimized table or not.
STEP 2)
Now, sql database administrators can run " DBCC IND " command with required parameters to list data pages for a given table in a specific database.
DBCC IND(KodyazSQLScripts, DataPages, -1)
In the above DBCC command the KodyazSQLScripts is the database name and DataPages is the SQL table name. The output list of the above command will result as seen in below screenshot.
Row with PagePID value 289 has PageType as 1 (data page) and includes data rows of the table which we are trying to display in this tutorial.
If you execute above DBCC command for a in-memory optimized table in SQL Server 2014 database, the output will return nothing. This is actually expected for in-memory optimized tables. Since data is stored in memory and not in physical disk like disk optimized tables, we don't have the data page structures for such in-memory optimized tables.
STEP 3)
Using the page file id and data page id values listed as the result set of DBCC IND command, sql programmers can now DBCC PAGE command to display data page contents. Here is a sample DBCC PAGE statement which is displaying the data page contents of database KodyazSQLScripts, page file id 1 and data page id 289
The last parameter in below usage is the printopt parameter. Please refer to DBCC PAGE syntax given above in this tutorial.
Print option parameter value 2 displays page header and the hex dump of the data page
Print option 3 will display page header and a more readable row key-value pair presentation.
DBCC PAGE (KodyazSQLScripts, 1, 289, 3) -- PageType 1
Here is the data page header section
SQL data page header displayed with DBCC PAGE statement
And below is pay load section where row contents are displayed per row stored in that data page
Row data displayed in data page pay load section using DBCC PAGE command
If you execute DBCC PAGE statement with print option parameter value equals to 1, DBA's and developers can see the row offset table in that data page as seen in below screenshot.
DBCC PAGE (KodyazSQLScripts, 1, 293, 1)
STEP 4)
After you have completed your task with DBCC trace flag, in our case trace flag 3604 you have to disable tracing using the DBCC TRACEOFF statement. This will prevent performance issues related due to DBCC command.
DBCC TRACEOFF(3604)
If you wonder if any trace flag is active on your session or globally activated, you can execute DBCC TRACESTATUS command. This will give you if there is any trace flags left enabled so you can disable them by using DBCC TRACEOFF statement.
DBCC TRACESTATUS
I hope I've demonstrated how the contents of a data page belonging to a database table can be displayed on SQL Server in this tutorial. Please do not forget that these DBCC commands are undocumented. It is your own risk to use these commands on productive SQL Server database servers.