Recently I had occasion to take a look at the deadlocks happening on one of our servers. I usually do this the easy way by running the sp_BlitzLock stored procedure placed out there by the wonderful people at Brent Ozar Unlimited.
This time though sp_BlitzLock was running for over an hour so I was guessing there was an issue. Turns out it was mostly on my side. I also have an Extended Events trace running just for deadlocks to make it easier for me to diagnose and I noticed one that was probably the reason that sp_Blitzlock was having trouble. In the visual representation it looks like this:
I can’t make heads or tails of that but I can tell you that seems like a really bad brawl for resources. It’s like a Jerry Springer show with a few extras thrown in. Since I knew that my graph wasn’t going to be helpful in this instance I went to the actual xml and tried to figure out how I could tune this to make it better in the future. I needed to know exactly where the issue was so the waitresource pointer is a good place to start.
You will see many blog articles on how to find SQL wait resources when the resource type is a key, a page, or an object (I suggest Kendra Little’s blog post) There is however a noticeable glut on articles explaining RID (a RID is a key on a table with no clustered index). I finally found how to tie a RID to an actual resource name but it was used for corruption so the details were a bit hazy at first.
Here is the full script that I was able to use in deciphering RID back to a named resource.
/* Break apart my RID into it's individual sections */
DECLARE @db_id SMALLINT = 15;
DECLARE @file_id SMALLINT = 15;
DECLARE @page_id INT = 11695844;
DECLARE @index_id SMALLINT = 3;
DECLARE @db_name VARCHAR(256) = (SELECT DB_NAME(@db_id))
/* Create and populate a temp table with the meta data from that data page */
CREATE TABLE #PageResults
( [ParentObject] NVARCHAR(255), [Object] NVARCHAR(255), [Field] NVARCHAR(255), [VALUE] NVARCHAR(255) );
INSERT INTO #PageResults
( ParentObject, [Object], Field, [VALUE] )
EXECUTE ('DBCC PAGE (' + @db_id + ', ' + @file_id + ', ' + @page_id + ', 0) WITH TABLERESULTS;');
/* Tie the metadata in the dbcc page output and give me my resource */
SELECT sc.name AS schema_name,
so.name AS object_name,
si.name AS index_name
FROM ' + @db_name + '.sys.partitions AS p
INNER JOIN ' + @db_name + '.sys.objects AS so
ON p.object_id = so.object_id
INNER JOIN ' + @db_name + '.sys.indexes AS si
ON p.index_id = si.index_id
AND p.object_id = si.object_id
INNER JOIN ' + @db_name + '.sys.schemas AS sc
ON so.schema_id = sc.schema_id
INNER JOIN #PageResults pr
ON so.object_id = pr.[VALUE]
WHERE si.index_id = ' + @index_id + '
AND Field = ''Metadata: ObjectId'';