Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using DBCC PAGE to Examine SQL Server Table and Index Data


By:   |   Updated: 2008-09-08   |   Comments (9)   |   Related: 1 | 2 | 3 | 4 | 5 | More > Database Console Commands DBCCs

Problem
I've read a lot of articles that talk about database pages and how they're used to store table data and index data. Is there some way I can actually look at this information?

Solution
One of the worst kept secrets in the SQL Server community is the undocumented DBCC PAGE command which allows you to examine the contents of data and index pages. Some of this information is a bit cryptic and your favorite search engine is a good place to start to find a lot of the meanings behind the output it displays. However, I find this DBCC command very handy when sleuthing around the database to troubleshoot database issues where I only have page information to go on or if I just want to see what the database engine is doing with data and index pages when certain database actions occur.

DBCC PAGE Parameters

DBCC PAGE
(
['database name'|database id], -- can be the actual name or id of the database
file number, -- the file number where the page is found
page number, -- the page number within the file
print option = [0|1|2|3] -- display option; each option provides differing levels of information
)
 

First, let's create a sample database and table that will help illustrate what you can see using DBCC PAGE:

USE MASTER
GO
CREATE DATABASE MSSQLTIPS
GO
USE MSSQLTIPS
GO
CREATE TABLE DBO.EMPLOYEE
(
EMPLOYEEID INT IDENTITY(1,1),
FIRSTNAME VARCHAR(50) NOT NULL,
LASTNAME VARCHAR(50) NOT NULL,
DATE_HIRED DATETIME NOT NULL,
IS_ACTIVE BIT NOT NULL DEFAULT 1,
CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPLOYEEID),
CONSTRAINT UQ_EMPLOYEE_LASTNAME UNIQUE (LASTNAME, FIRSTNAME)
)
GO
INSERT INTO DBO.EMPLOYEE (FIRSTNAME,LASTNAME,DATE_HIRED)
SELECT 'George', 'Washington', '1999-03-15'
GO
INSERT INTO DBO.EMPLOYEE (FIRSTNAME,LASTNAME,DATE_HIRED)
SELECT 'Benjamin', 'Franklin', '2001-07-05'
GO
INSERT INTO DBO.EMPLOYEE (FIRSTNAME,LASTNAME,DATE_HIRED)
SELECT 'Thomas', 'Jefferson', '2002-11-10'
GO

We're now ready to take a look and see how SQL Server has stored the data and index pages. But where do we start? Where can we find where the pages for this table and its data live? Well, there's another undocumented DBCC command - DBCC IND - that you can use to list all of a table's data and index pages.

DBCC IND Parameters

DBCC IND
(
['database name'|database id], -- the database to use
table name, -- the table name to list results
index id, -- an index_id from sys.indexes; -1 shows all indexes and IAMs, -2 just show IAMs
)

Let's run the following command to list out the EMPLOYEE table's page structure

-- List data and index pages allocated to the EMPLOYEE table
DBCC IND('MSSQLTIPS',EMPLOYEE,-1)
GO

Here is what was output for my database:

Note: For brevity, the figure above only shows the first 11 columns output by the DBCC command. There are additional columns that contain linked list information which allow you to see how the pages link to each other.

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.

With this information, we're now ready to look at how our inserted rows for Washington, Franklin, and Jefferson are stored in the EMPLOYEE table. The EMPLOYEE table has a clustered index (courtesy of the PRIMARY KEY definition) which means that there should be an IndexID = 1 produced by the DBCC IND output (an index_id = 1 points to the clustered index where the leaf level pages are the actual data for the table). Looking at the output produced by DBCC IND, we can see that the clustered index, which is also a PageType = 1, can be found within file number (PageFID) = 1 and page number (PagePID) = 143. There are 4 different print options for displaying the page data. The one I use is print option 3 which dumps both page header information and data.

Note: Before we can run DBCC PAGE, it's required that trace flag 3604 be set to instruct the engine to send output to the console; otherwise you won't see anything!

DBCC TRACEON(3604)
DBCC PAGE('MSSQLTIPS',1,143,3) WITH TABLERESULTS
GO

Scrolling to the end of the results, we can see our data stored and it's stored in the order of the clustered index (EMPLOYEEID). The data rows are stored in slots that start with a zero offset.

 

The EMPLOYEE table also has a non-clustered index (via the UNIQUE constraint that was defined on the table). Let's examine the non-clustered index that was built. Looking at DBCC IND output again, we can easily determine the non-clustered index page since it is IndexID = 2 (PageType = 2) and that it can be found within file number (PageFID) = 1 and page number (PagePID) = 153. Note that if we had multiple indexes on this table, we could look in sys.indexes and get the index_id which we could then use to examine a specific index. Now let's examine the index data:

DBCC PAGE('MSSQLTIPS',1,153,3) WITH TABLERESULTS
GO

Scrolling to the end of these results, we can see our index data is stored in logical order by lastname and firstname. You should also make note that the clustered index key is also stored within the index rows. This is used by the engine for navigating through the clustered index when a bookmark lookup is required (this type of lookup occurs when the index columns do not contain all the columns necessary to satisfy a query).

 

If there was no clustered index on the table, this additional column would point to the actual data page instead. Let's rebuild the PRIMARY KEY as a non-clustered index and re-examine the non-clustered index created by the UNIQUE constraint. Note that by rebuilding the table with no clustered index, the underlying page data has changed. Using DBCC IND, you can see how the page structures have changed.

ALTER TABLE DBO.EMPLOYEE DROP CONSTRAINT PK_EMPLOYEE
GO
ALTER TABLE DBO.EMPLOYEE ADD CONSTRAINT PK_EMPLOYEE
PRIMARY KEY NONCLUSTERED (EMPLOYEEID)
GO
DBCC IND('MSSQLTIPS',EMPLOYEE,-1)
DBCC PAGE('MSSQLTIPS',1,155,3) WITH TABLERESULTS
GO

 



As you can see, the indexes in a HEAP table (a table that's not clustered) store a different pointer that points directly to the page that contains the additional data required.

These were simple examples but they should be enough to give you an idea of how and where to get and display table and index data. In future tips, I will go over examples that will show what happens when a row is modified and it cannot fit on a page, what happens when rows are deleted, and how DBCC PAGE can help in troubleshooting some blocking and deadlock situations.

I have to stress that DBCC IND and DBCC PAGE are officially undocumented and could disappear in a future SQL Server version. Until that happens, I will continue to utilize these commands as primary tools in peeking into the engine's data storage techniques and for troubleshooting SQL Server issues.

Next Steps

  • Since the DBCC IND and DBCC PAGE commands are undocumented, one of the best sources for finding addtional information on these commands are the MSDN blogs
  • Explore DBCC PAGE and it's print options against your own database tables and indexes
  • Read about Table and Index Architecture in the SQL Server Books Online
  • Stay tuned for additional tips on DBCC PAGE


Last Updated: 2008-09-08


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, May 07, 2019 - 11:14:23 AM - Jeff Moden Back To Top

I know this is a decade old post but it's not necessary at all to use the Trace Flag.  The "WITH TABLERESULTS" by itself does the job quite nicely and also keeps your SQL Server Error Log from being filled up with a bunch of "On" and "Off" messages for the Trace Flag.


Friday, October 13, 2017 - 3:30:13 PM - Ram Back To Top

 

 

Great Article. Excellent work


Tuesday, September 22, 2015 - 12:48:40 PM - govind Back To Top

 

Hi Armando,

 

I am facing many deadlock issue on server, update and delete triggers has been used which accessing one table and acquiring page lock on it, two pages gets locked by each other. If I go through dbcc ind() info, I can see that both pages acquired by same table and table is having primary key on it. so in such case how we can use dbcc page() for troubleshooting deadlock?

Thanks,

Govind Mayekar

 


Sunday, October 13, 2013 - 12:49:03 AM - John G Back To Top

Thanks Armando! With the information in this tip I was able to grab the data out of a corrupted page before running DBCC CHECKTABLE repaire_allow_data_loss. The page was dropped during the repair but we can restore it from the output of DBCC PAGE and a little elbow grease.

Great tip!


Wednesday, July 24, 2013 - 2:36:00 AM - selvakumar Back To Top

Excellent Armando:) Its increase my curiosity to learn more about Allocation unit.


Wednesday, March 20, 2013 - 10:42:11 AM - Graeme Martin Back To Top

Quasi-official documentation: http://support.microsoft.com/kb/83065. However, from an ANCIENT version of SQL Server (SYBASE)


Thursday, April 12, 2012 - 8:34:23 AM - Greg Schroder Back To Top

Excellent example, but I am missing two things.

Firstly, this example does not have any index pages for the clustered index (i.e. index_id = 1, page_type = 2). Is this because it is a table with one row, so there is no root node or intermediary nodes? The root is is a leaf node (i.e. page_type = 1, a data page).

Secondly, how is the index linked to it's root page? Can't find the link. Obviously looking at DBCC IND results, the row with highest index_level, and no previous pages (i.e. PrePagePID). Looking at one of my tables, I have pages with page_type=2 index_level=1, and pages with page_type=1 index_level=0. But where is the root page on index_level=2, to link to the index pages on index_level=1?


Thursday, April 12, 2012 - 6:37:48 AM - manu Back To Top

Really helpful. Keep going.


Thursday, April 12, 2012 - 3:50:04 AM - john the rule Back To Top

 

 

Very Godd Example .


Learn more about SQL Server tools