If you use the blocked process report or collect the deadlock graphs provided by SQL Server periodically, you will encounter things like this:
waitresource="PAGE: 6:3:70133"
waitresource=βKEY: 6:72057594041991168 (ce52f92a058c)β
Sometimes, there will be more information in that giant XML that you study (deadlock graphs contain a list of resources that helps you find out the names of the object and index), but not always.
This text will help you decipher them.
All the information that is here is on the Internet in various places, it is simply very distributed! I want to put it all together, from DBCC PAGE to hobt_id to the undocumented %%physloc%% and %%lockres%% functions.
First, let's talk about waits on PAGE locks, and then move on to KEY locks.
1) waitresource="PAGE: 6:3:70133" = Database_Id: FileId: PageNumber
If your request is waiting on a PAGE lock, SQL Server will give you the address of that page.
Breaking down "PAGE: 6:3:70133" we get:
- database_id = 6
- data_file_id = 3
- page_number = 70133
1.1) Decrypt database_id
Find the name of the database using the query:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOIt's public on my SQL Server.
1.2) Looking for the name of the data file - if you're interested
We are going to use data_file_id in the next step to find the table name. You can just skip to the next step, but if you are interested in the file name, you can find it by running a query in the context of the found database, substituting data_file_id into this query:
USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GOIn the WideWorldImporters database this is a file called WWI_UserData and I have it restored to C:MSSQLDATAWideWorldImporters_UserData.ndf. (Oops, you caught me putting files on the system drive! No! It's embarrassing).
1.3) Get object name from DBCC PAGE
We now know that page #70133 in datafile 3 belongs to the WorldWideImporters database. We can look at the contents of this page using the undocumented DBCC PAGE and trace flag 3604.
Note: I prefer to use DBCC PAGE on a restored copy somewhere on another server, because it's undocumented stuff. In some cases, she (approx. translator - the link, unfortunately, leads nowhere, but judging by the url, we are talking about filtered indexes).
/* This trace flag makes DBCC PAGE output go to our Messages tab
instead of the SQL Server Error Log file */
DBCC TRACEON (3604);
GO
/* DBCC PAGE (DatabaseName, FileNumber, PageNumber, DumpStyle)*/
DBCC PAGE ('WideWorldImporters',3,70133,2);
GO
By scrolling to the results, you can find the object_id and index_id.
Almost done! Now you can find the table and index names with a query:
USE WideWorldImporters;
GO
SELECT
sc.name as schema_name,
so.name as object_name,
si.name as index_name
FROM sys.objects as so
JOIN sys.indexes as si on
so.object_id=si.object_id
JOIN sys.schemas AS sc on
so.schema_id=sc.schema_id
WHERE
so.object_id = 94623380
and si.index_id = 1;
GOAnd here we see that the wait on the lock was on the PK_Sales_OrderLines index of the Sales.OrderLines table.
Note: In SQL Server 2014 and above, the object name can also be found using the undocumented DMO sys.dm_db_database_page_allocations. But you have to query every page in the database, which doesn't look very cool for large databases, so I used DBCC PAGE.
1.4) Is it possible to see the data on the page that was blocked?
Nuuu, yes. But... are you sure you really need it?
It's slow even on small tables. But it's kind of cool, so since you've read this far...let's talk about %%physloc%%!
%%physloc%% is an undocumented piece of magic that returns a physical ID for each entry. you can use .
Now that we know that we wanted to block the page in Sales.OrderLines, we can look at all the data in this table, which is stored in data file #3 on page #70133, with the following query:
Use WideWorldImporters;
GO
SELECT
sys.fn_PhysLocFormatter (%%physloc%%),
*
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GOAs I said, it's slow even on tiny tables. I added NOLOCK to the request because we still have no guarantee that the data we want to look at is exactly the same as it was at the time the lock was discovered - so we can safely do dirty reads.
But, hooray, the query returns me the very 25 rows for which our query fought.
Enough about PAGE locks. What if we are waiting for a KEY lock?
2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (magic hash that can be decrypted with %%lockres%% if you really want to)
If your query tries to lock on an index entry and gets locked on its own, you get a completely different type of address.
Breaking β6:72057594041991168 (ce52f92a058c)β into parts, we get:
- database_id = 6
- hobt_id = 72057594041991168
- magic hash = (ce52f92a058c)
2.1) Decrypt database_id
It works exactly the same as with the example above! We find the name of the database using the query:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOIn my case, it's the same .
2.2) Decrypt hobt_id
In the context of the found database, you need to query sys.partitions with a couple of joins that will help determine the names of the table and index ...
USE WideWorldImporters;
GO
SELECT
sc.name as schema_name,
so.name as object_name,
si.name as index_name
FROM sys.partitions AS p
JOIN sys.objects as so on
p.object_id=so.object_id
JOIN sys.indexes as si on
p.index_id=si.index_id and
p.object_id=si.object_id
JOIN sys.schemas AS sc on
so.schema_id=sc.schema_id
WHERE hobt_id = 72057594041991168;
GOIt tells me that the request was waiting on the Application.Countries lock using the PK_Application_Countries index.
2.3) Now for some %%lockres%% magic - if you want to find out which entry was locked
If I really want to know on which row the lock was needed, I can find out by querying the table itself. We can use the undocumented %%lockres%% function to find an entry that matches the magic hash.
Note that this query will scan the entire table, and on large tables this might not be fun at all:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO
I added NOLOCK () because locks can be a problem. We just want to look at what is there now, and not what was there when the transaction began - I donβt think that data consistency is important to us.
Voila, the record we fought for!
Acknowledgments and further reading
I don't remember who first described many of these things, but here are two posts about the least documented things you might like:
- Paul Randal's post about (as we have our data in the first example)
- Question on StackOverflow about (as we found the data in the second example). One of the answers leads to the post .
Source: habr.com