First published on MSDN on Oct 16, 2018
Microsoft Ignite 2018 is fading away in the rearview mirror, but the excitement over all the new features and the vision shared there is just beginning. You're probably seeing lots of buzz around the new
Big Data Clusters
, data virtualization and
Intelligent Query Processing
among others, but I wanted to take some time on the blog to dig a little deeper into a few of the improvements we're working on in the storage engine. The first feature is one which I'm particularly excited about – the new
sys.dm_db_page_info
dynamic management function!
The primary use case we had in mind when developing this function was troubleshooting page-related waits. Some of the key performance scenarios that require page details to diagnose are
tempdb contention
,
last page insert contention
(also see
this blog article
for code samples) and page-level
blocking
. All of these scenarios present as either PAGELATCH or lock wait types in sys.dm_exec_query_stats with a page identifier (db_id:file_id:page_id) in the wait_resource column. In order to gain any insight into the problem, you have to know more details about what that page is. Is it a data page, an index page or something else? If it's a data or index page, which object does it belong to? Prior to SQL Server 2019 CTP 2.0, the only way to gain any of this insight was to use the
lightly
documented command DBCC PAGE. Unfortunately, if you're using some sort of monitoring tool or script to gather data about waits and blocking, there's no good way to include this DBCC PAGE call in those scripts. Not to mention it requires sysadmin privileges. Enter sys.dm_db_page_info.
The sys.dm_db_page_info function takes 4 parameters: database ID, file ID, page ID and mode ('LIMITED' or 'DETAILED'). It returns a table with a single row that contains the header information from the page, including things like object_id, index_id and partition_id. As a standalone function, this provides a documented and fully supported way to retrieve this information without the need for a DBCC command. The real benefit however is the ability to JOIN this function with other DMVs such as sys.dm_exec_requests or sys.sysprocesses to gather this information automatically when there is any sort of page-related contention. If you're familiar with these views you know that the wait_resource column can contain all sorts of different resources, so it's not in the right format to facilitate a direct join to sys.dm_db_page_info. To support this use case, we had to add another column to these views called page_resource. Whenever the wait_resource column contains a page resource, the page_resource column will be populated with an 8-byte hexadecimal value. The page ID is the first four bytes, followed by 2 bytes for the file ID and then 2 bytes for the database ID.
For example:
0x4801000001000500 translates to page 5:1:328.
Don't worry, you don't have to write T-SQL to do this translation yourself, we've created a new companion function called
sys.fn_PageResCracker
that will do this translation for you. Here's a sample script of how you can use these two new functions to help troubleshoot page-related waits:
SELECT page_info.*
FROM sys.dm_exec_requests AS d
CROSS APPLY sys.fn_PageResCracker(d.page_resource) AS r
CROSS APPLY sys.dm_db_page_info(r.db_id, r.file_id, r.page_id, 1) AS page_info
Now that you know the primary use case, let's discuss some limitations. First, this is not a full replacement for DBCC PAGE. DBCC PAGE gives you the entire contents of the page, including header information as well as all the data and the slot array. It can also interpret all this information for you and return it in a user-friendly manner. There are still use cases for this, but they are primarily for things like deep troubleshooting, data corruption analysis and recovery, or simply to illustrate SQL Server concepts such as how indexes are structured. I fully expect to continue to see DBCC PAGE used for demos and illustration purposes, but hopefully its production use can be limited to only those scenarios that absolutely require it.
Another limitation is that we currently only support joins to sys.dm_exec_requests and sys.sysprocesses. We hope to expand this list in the future and are open to suggestions for where you would like to see the page_resource column added. One additional view we have in mind is
sys.dm_os_waiting_tasks
, but since this view stores wait resource information differently than sys.dm_exec_requests, it requires a little more work. Please feel free to comment here or send us feedback at
https://aka.ms/sqlfeedback
to let us know how you'd like to use this function in the future.
