Did you ever use the famous DBCC PAGE command? Folks who are interested in digging further to the SQL Server storage already use it for a while. We also use it during our SQL Server performance workshop by the way. But the usage of such command may sometimes go beyond and it may be used for some troubleshooting scenarios. For instance, last week, I had to investigate a locking contention scenario where I had to figure out which objects were involved and with their related pages (resource type) as the only way to identify them. SQL Server 2019 provides the sys.dm_db_page_info system function that can be useful in this kind of scenario.
To simulate locks let’s start updating some rows in the dbo.bigTransactionHistory as follows:
USE AdventureWorks_dbi; GO BEGIN TRAN; UPDATE TOP (1000) dbo.bigTransactionHistory SET Quantity = Quantity + 1
Now let’s take a look at the sys.dm_tran_locks to get a picture of locks held by the above query:
SELECT resource_type, COUNT(*) AS nb_locks FROM sys.dm_tran_locks AS tl WHERE tl.request_session_id = 52 GROUP BY resource_type
Referring to my customer scenario, let’s say I wanted to investigate locks and objects involved. For the simplicity of the demo I focused only the sys.dm_tran_locks DMV but generally speaking you would probably add other ones as sys.dm_exec_requests, sys.dm_exec_sessions etc …
SELECT tl.resource_database_id, SUBSTRING(tl.resource_description, 0, CHARINDEX(':', tl.resource_description)) AS file_id, SUBSTRING(tl.resource_description, CHARINDEX(':', tl.resource_description) + 1, LEN(tl.resource_description)) AS page_id FROM sys.dm_tran_locks AS tl WHERE tl.request_session_id = 52 AND tl.resource_type = 'PAGE'
The sys.dm_tran_locks DMV contains the resource_description column that provides contextual information about the resource locked by my query. Therefore the resource_description value column will inform about [file_id:page_id] when resource_type is PAGE.
SQL Server 2019 will probably lead the DBCC PAGE command to return to the stone age for some tasks but let’s start with this old command as follows:
DBCC PAGE (5, 1, 403636, 3) WITH TABLERESULTS;
The DBCC PAGE did the job and provides and output that includes the page header section where the Metadata: ObjectId is stored. We may then use it with OBJECT_NAME() function to get the corresponding table name.
But let’s say that using this command may be slightly controversial because this is always an undocumented command so far and no need to explain here how it can be dangerous to use it in production. Honestly, I never encountered situations where DBCC PAGE was an issue but I may not provide a full guarantee and it is obviously at your own risk. In addition, applying DBCC PAGE for all rows returned from my previous query can be a little bit tricky and this is where the new sys.dm_db_page_info comes into play.
;WITH tran_locks AS ( SELECT tl.resource_database_id, SUBSTRING(tl.resource_description, 0, CHARINDEX(':', tl.resource_description)) AS file_id, SUBSTRING(tl.resource_description, CHARINDEX(':', tl.resource_description) + 1, LEN(tl.resource_description)) AS page_id FROM sys.dm_tran_locks AS tl WHERE tl.request_session_id = 52 AND tl.resource_type = 'PAGE' ) SELECT OBJECT_NAME(page_info.object_id) AS table_name, page_info.* FROM tran_locks AS t CROSS APPLY sys.dm_db_page_info(t.resource_database_id, t.file_id, t.page_id,DEFAULT) AS page_info
This system function provides a plenty of information mainly coming from the page header in tabular format and makes my previous requirement easier to address as show below.
The good news is this function is officially documented but un/fortunately (as you convenience) for the deep dive study you will still continue to rely on the DBCC PAGE.