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.

blog 148 - 0 - banner

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

blog 148 - 1 - query locks

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'

blog 148 - 2 - locks and pages

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;

blog 148 - 3 - dbcc page

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.

SELECT OBJECT_NAME(695673526)

blog 148 - 4 - dbcc page - object_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.

blog 148 - 5 - sys.dm_db_page_info

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.

Happy troubleshooting!

By David Barbarin