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'
GOWe’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)
GOHere 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.
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
GOScrolling 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
GOScrolling 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
