Why is DBCC PAGE useful?
Even with the improvements in technology most performance bottlenecks still occur within the storage layer. For this reason it can be very useful for a SQL Engineer to look into the way that data is actually stored within the database pages. To do this we need a function, and this is what DBCC PAGE offers us.
If you are new to SQL Server's storage structure then you should refer to BOL however the following is an extract from http://msdn.microsoft.com/en-US/library/ms190969(v=sql.105).aspx
The fundamental unit of data storage in SQL Server is the page. The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages numbered contiguously from 0 to n. Disk I/O operations are performed at the page level. That is, SQL Server reads or writes whole data pages.
Extents are a collection of eight physically contiguous pages and are used to efficiently manage the pages. All pages are stored in extents.
As I mentioned DBCC PAGE is an undocumented command, so this does come with no-warranty and won't be supported if you try and call support..... use at your own risk..... so be careful if you start using it on production!
Interpreting DBCC Page Output
The first step before we run DBCC PAGE is that we must find out the Page ID that we are interested in looking at the DMF sys.dm_db_database_page_allocations as this will provide us the information we require. This DMF takes the following parameters.
sys.dm_db_database_page_allocations([DatabaseID], [TableID], [PartitionID], [IndexID], [Mode])
In earlier releases of SQL Server we had to use the DBCC IND([Database], [Table], [Unk],[Unk]) to obtain this information and the Page Id.
For example if we wanted to return all pages from the table dbo.Test then we would use the following query:
SELECT *
FROM sys.dm_db_database_page_allocations(db_id('FragTest'), object_id('dbo.Test'), NULL, NULL, 'DETAILED')
As this is a dynamic management function we can also restrict the results using a typical where clause such as the following which ensures we only see pages stored at the leaf level:
SELECT *
FROM sys.dm_db_database_page_allocations(db_id('FragTest'), object_id('dbo.Test'), NULL, NULL, 'DETAILED')
WHERE page_level = 0
While there are many important columns returned by this data, some of the main columns of interest are the following:
| Column | Description |
| database_id | The database that the page belongs to. Only really relevant if you do not provide the Database ID to the DMF. |
| object_id | The Object ID that owns the page. |
| index_id | The index ID that the page belongs to (e.g. 0 = heap, 1 = clustered, 2...etc) |
| partition_id | The partition id within the database that this page is created in. |
| extent_file_id | The file ID that this page has been created in. |
| allocated_page_iam_page_id | The Page ID for the IAM managing this page. |
| allocated_page_page_id | The Page ID. We need this when we perform the next steps. |
| page_type | The numerical value that represents the page type. Refer below. |
| page_type_desc | The friendly name for the page type (e.g. DATA_PAGE). Refer below. |
| page_level | Describes the level that page exists at (e.g. 0 = Leaf level or Heap level in non-btree, 1 = Intermediate Level of the index, >=2 Additional Intermediate level or if the highest level then the root node. |
Once we have found this information we can start to dig further into the page we wish to view. But how do we identify which page to look at.... I'll cover that in another blog where I will explain the levels etc. For this post lets just take a DATA Page and view that, but how do we do that.
Looking at a cut down column list of the above query:
We can see we have 2 indexes related to the table dbo.Test, a Clustered Index and a Non-Clustered index based on the Index_Id column. So we will pick a DATA_PAGE from each.
Before we can run the DBCC PAGE command we need to enable a traceflag to that the output of the command is returned to the client, otherwise it is only written to the SQL ERRORLOG file.
DBCC TRACEON (3604);
First lets look at page 280 and so we will use the DBCC PAGE function but we need to supply the following params:
DBCC PAGE([Database], [FileID], [Allocated_Page_Page_ID], [View])
The view parameter takes a value from 0 to 3. A value of 0 returns only the Page Header info, a value of 1 or 2 will include the Offset table, and 3 includes all of the details of the slots. Each view offers it's own benefits so playing around with which one suits your purpose or provides you the information is recommended.
For example:
DBCC PAGE('FragTest',1,280, 3)
The output of this command is where it starts to get detailed as it is reported in Text via the Messages tab. In this case we are looking at the Clustered Index (or Heap) but if we were looking at the Non-Clustered index (as we will below) we would also receive a Result set containing the table of key values and their hash values used by SQL Server to traverse through the B-Tree or Heap structures.
NOTE: This is only the Page Header section of the page shown here.
As you can see there is quite a bit of information within the output. To help you identify certain parts of data the following has been provided.
| Field | Description |
| m_level | Values indicate if the page is located at the Leaf (0), Intermediate (1), or Root level. |
| m_type | (see table blow) |
| Metadata: ObjectId | The object_id of the object which owns the page (e.g. a table's object id) |
| Metadata: IndexId | Value indicates the ID of index (zero = heap, 1 = clustered, >=2 non-clustered) |
| m_slotCnt | Number of slots in use on this page |
| m_freeCnt | Space free within page (devide this by average slot record size's to see how many more slots/rows can be added before a new page is required |
The following table outlines the numerical values for the m_type otherwise known as the Page Type and their corresponding Name and Description to provide an easy reference when trying to determine which page you are viewing from the PAGE function output.
| Value | Name | Description |
| 1 | DATA_PAGE | Holds the records |
| 2 | INDEX_PAGE | B-tree (Clustered Index) records |
| 8 | GAM_PAGE | file allocation map page (free extents in a file) |
| 9 | SGAM_PAGE | small extent file allocation map page (extents supporting single page allocation) |
| 10 | IAM_PAGE | scanable object allocation map page (extents allocated to a given object) |
| 11 | PFS_PAGE | space usage on a page by page basis as well as allocation state |
As mentioned above the screen shot of the DBCC PAGE output is only the Page Header. If you included the View parameter of 1 or 2 then you would also have the OFFSET TABLE at the end of the output which looks like:
This table is how the Database Engine locates the specific row within the Page as the offset references the Slots within the Page which are also included in the output like the following:
NOTE: The actual output will vary depending on the view mode supply to the PAGE function, or the actual data.
As I mentioned this is the output we receive when looking at the Clustered Index or Heap data, but what about if we look at Non-Clustered data pages. So lets look at page 296 and this time we will use a different view mode param.
DBCC PAGE('FragTest',1,296, 3)
While most output is similar under the Messages tab, we now receive the Results table with a record set describing the Index Key, related key from the Clustered Index (or RID if supported by a Heap table), as well as a Unique Qualifier if we have not specified the index key to be unique because for SQL Server to lock the resource this is required. We are also presented with the Hash key but that is used for resource locking and a whole other discussion.
So that is the basics for interpreting the output of the DBCC PAGE command. Of cause once you have the basics you can really get deep into the structure of your data and it can become much more complex, but I hope this guide at least provides a grounding to allow you become more familiar with the structure.
I will be posting more about interpreting this data along with the B-Tree or Heap structures, and that process will go much deeper into this topic.
Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.
No comments:
Post a Comment