As I promised a couple of weeks ago, this post will show you how to look inside a page to see what is actually stored there. I’ll just be looking at data pages, and only a very basic example, just so you get the idea. But once you get familiar with looking inside pages, there are many mysteries about SQL Server data storage that you can solve on your own. I’ll mention a few of them at the end of this post. In addition, looking at index pages can also be quite educational, but I’ll have to save that for another time. And remember that if the table has a clustered index, the data pages are also technically index pages, but as far as the tools I’m going to show you work, a page at the leaf level of a clustered index is a data page, and not an index page.
The tool we’re going to use is the undocumented DBCC PAGE command. There is no DMV yet for this DBCC command, like DBCC IND became sys.dm_db_database_page_allocations and DBCC SHOWCONTIG became sys.dm_db_index_physical_stats. Even though it’s officially undocumented, an online search can help you find other articles and blog posts that describe DBCC PAGE in detail; additionally, I provide lots of details in my SQL Server Internals books.
I’m going to use a very small table from the AdventureWorks database for the basic exploration of the contents. (Either AdventureWorks2104 or AdventureWorks2016 should work.) We’ll also take a quick look at a bigger table so you can see the Next and Previous page values.
I’m going to create a copy of Sales.Territory as an empty table. Usually just doing a SELECT INTO with a TOP 0 can copy the empty table, but I want to remove the IDENTITY property from the TerritoryID column. To do that, I use a little trick. If a SELECT INTO is based on a UNION, certain column properties, including IDENTITY, will not be inherited, so my code to create an empty table with the same column definitions, except for IDENTITY, is shown below. I then build a clustered index on the table and copy the data from the original table.
1 2 3 4 5 6 7 8 9 10 11 12 13 | USE AdventureWorks2014; GO DROP TABLE IF EXISTS Territories; GO SELECT TOP 0 * INTO Territories FROM Sales.SalesTerritory UNION SELECT TOP 0 * FROM Sales.SalesTerritory; GO CREATE CLUSTERED INDEX TerritoryNameIndex ON Territories(Name); GO INSERT INTO dbo.Territories SELECT * FROM sales.SalesTerritory; GO |
So why didn’t I just copy the data as I created the table, and left off the first TOP 0? I wanted to create the clustered index on the table before I populated it, so that I could show you that even with the clustered index, the data is not stored in physical order on the page.
So now let’s use DBCC PAGE to look at the contents of the page storing the Territories data rows. Here’s the syntax:
1 | DBCC PAGE ({dbid | dbname}, filenum, pagenum[, printopt]) |
We can get the filenum and pagenum values using one of the methods I showed you in this post.
The printopt parameter can have one of these values:
- 0 Default; prints the buffer header and page header
- 1 Prints the buffer header, page header, each row separately, and the row offset table
- 2 Prints the buffer and page headers, the page as a whole, and the offset table
- 3 Prints the buffer header, page header, each row separately, and the row offset table; each row is followed by each of its column values listed separately
In addition, DBCC PAGE must have Traceflag 3604 enabled. Without this traceflag, no output is returned to the client for the DBCC PAGE command. I’ll use sys.dm_db_database_page_allocations, and take the values I get back to plug into the DBCC PAGE command:
1 2 3 4 | SELECT allocated_page_file_id AS filenum, allocated_page_page_id AS pagenum FROM sys.dm_db_database_page_allocations(db_id('AdventureWorks2014'), object_id('Territories'), 1, NULL, 'DETAILED') WHERE page_type = 1; |
Here are my results:
So now we can execute DBCC PAGE:
1 2 | DBCC TRACEON(3604); DBCC PAGE (Adventureworks2014, 1, 24520, 1); |
IF you run this DBCC PAGE command using the page address from your table, you’ll see that the output is divided into four main sections: BUFFER, PAGE HEADER, DATA, and OFFSET TABLE (really, the offset array). The BUFFER section shows information about the buffer for the given page. A buffer in this context is the in-memory structure that manages a page, and the information in this section is relevant only when the page is in memory.
The PAGE HEADER section in the output from DBCC PAGE displays the data for all the header fields on the page. Here is the page header in my output:
The m_slotcount value indicates the number of rows on the page, and my output shows all 10 rows in the Territories table are on this one page.
The DATA section contains information for each row. Using DBCC PAGE with a printopt value of 1 or 3 indicates the slot position of each row, the offset of the row on the page, and the length of the row. The row data is divided into three parts.
- The left column indicates the byte position within the row where the displayed data occurs.
- The middle section contains the actual data stored on the page, displayed in five columns of eight hexadecimal digits each.
- The right-most column contains an ASCII character representation of the data. Only character data is readable in this column, although some of the other data might be displayed.
Here are the first two rows in my output:
The first row contains the name ‘Australia’ and the second contains the name ‘Canada’, which we can see in the right-most column. As I built my clustered index on the name column, you might think this indicates that the data is actually stored in name order. But look at the value for Offset. The first row is at 0x420 and the second is at 0x2d2, which is a smaller number, earlier on the page. I’m not going to convert those hex values to decimal, because SQL Server does it for you in the last section. The last section, the OFFSET TABLE, shows the contents of the row offset array at the end of the page, which indicate the starting position of each row on the page.
In the output from DBCC PAGE, you can see that this page contains 10 rows, with the first row (indicated by Slot 0) beginning at offset 1056 (or 0x420) and the second row at offset 722 (0x2d2). The first row physically stored on the page is actually row 6, with an offset shown in the row OFFSET TABLE of 96. (The header for the page is always 96 bytes, so the rows start right after that.) DBCC PAGE with a printopt value of 1 displays the rows in slot number order, even though, as you can see by the offset of each of the slots, it isn’t the order in which the rows physically exist on the page. So even though there is a clustered index on the table, the rows are not physically stored on the page in clustered index order.
If you ran the same DBCC PAGE command with a printopt value of 2, you would see a dump of all 8,192 bytes on the page in the order they are actually stored. By looking at the third column that shows the character values in ASCII, you’ll see the that the name values are definitely NOT in order on the page.
I’ve already shown you that you can use the output of sys.dm_db_database_page_allocations to see the next page and previous page for any page in an index, where ordering is maintained. You can also see the page linkage in the PAGE HEADER area of the DBCC PAGE output.
In my database, I’ll look at the pages for the Sales.SalesOrderHeader table, and grab page 14831. Your table may have different page numbers.
1 2 3 4 5 6 7 8 | SELECT allocated_page_file_id AS filenum, allocated_page_page_id AS pagenum FROM sys.dm_db_database_page_allocations(db_id('AdventureWorks2014'), object_id('Sales.SalesOrderHeader'), 1, NULL, 'DETAILED') WHERE page_type = 1; GO DBCC TRACEON(3604); DBCC PAGE (AdventureWorks2014, 1, 14831, 1); GO |
Here is the PAGE HEADER section:
The m_prevPage value shows that the previous page in sorted data sequence is the previous page on the file, but the m_nextPage value indicates the next page in sorted sequence is not the current page number (14831) plus one.
Hopefully, these short examples will give you an idea of how you can use these tools to see how your data is actually organized on disk. In addition to verifying for yourself whether or not your data is actually physically sorted based on your clustered index key, there are other questions you could try to figure out the answers to. Consider this list as a set of homework exercises. Or, feel free to think of some examples of your own of how using these tools can provide useful information.
- When inserting data into a full page of a table with a clustered index, does the page always split exactly in half?
- What happens to your data rows when a table is altered to add a new column? What about dropping or altering a column?
- When rows are deleted, are they immediately removed from the data page?
