Identify SQL Server Locking and Blocking for Rows, Tables and Objects

Script to Decode SQL Server Locking and Blocking Wait Resources

By: Eric Blinn | Updated: 2023-05-15 | Comments (1) | Related: 1 | 2 | 3 | 4 | 5 | 6 | > Locking and Blocking

Problem

I'm experiencing blocking on my SQL Server instance. I know users are being blocked because I can see a SPID listed in the "blocked" column of sys.sysprocesses. Can I figure out who and what they are waiting on?

Solution

Locking and blocking are a natural part of any relational database management system such as SQL Server. Without locking potential subsequent blocking, the software could not guarantee the consistency of the data. This means that blocking isn't inherently a bad thing. It is only an issue when locks are held for a long time, making a noticeable impact on end-user experiences. If that happens, the DBA needs to determine why this is happening. One of the first steps is to figure out what object is being locked.

This tip will focus on determining what file, table, row, or other object is locked and causing the blocking.

When a SQL Server process is being blocked, it will go to an "LCK" wait type such as "LCK_M_IS" or "LCK_M_U". At the same time, a "waitresource" will be defined. The definition of waitresource from Microsoft's online documentation states, "If the request is currently blocked, this column returns the resource for which the request is currently waiting."

This "waitresource" is a text value indicating the specific file, table, row, or other object being locked and preventing it from moving forward. These values can be found in sys.sysprocesses, sys.dm_exec_requests, sp_whoisactive, traces, deadlock reports, and blocked process reports.

At first glance, waitresource values can seem cryptic, but they can be easily turned into a much more human-readable value.

Anatomy of a Wait Resource

The wait resource is the last value in this screenshot showing the text TAB: 8:2018106230:0.

The first few characters are letters and indicate the type of resource being locked. That will be followed by a colon (:), which separates a subsequent series of numbers separated by even more colons. There may be an alphanumeric code at the end. The rules for the numbers and codes are slightly different for each letter grouping, and this tip will have a section for each.

Decoding Wait Resource

TAB or OBJECT

The wait type prefixes of TAB and OBJECT are synonymous. TAB will appear in sys.sysprocesses while OBJECT will be returned by the more modern sys.dm_exec_requests. The screenshot in the previous section shows a TAB waitresource, meaning it came from sys.sysprocesses.

TAB is short for Table but doesn't necessarily mean a table-level lock is being held. While a table is the most common object type referenced by TAB or OBJECT, personally, I've seen a trigger or a stored procedure at fault.

TAB or OBJECT wait resources are an incredibly common lock type to find when a SQL Server is acting up. If a query unexpectedly performs a table scan, it can lock the entire table and cause other users to be blocked even if they only want to update a single row.

The first number after the word TAB or OBJECT, and between the first two colons (:) indicates which database the object is in. In this example, that value is 8.

Use that value to determine a database using a query like this: TAB: 8:2018106230:0

SELECT name FROM sys.databases WHERE database_id = 8;

Now that the database name is known (WideWorldImporters), the next number comes into play.

The value between the next set of colons is the object id: TAB: 8:2018106230:0

USE WideWorldImporters;

SELECT SCHEMA_NAME(schema_id) TableSchema, name TableName
FROM sys.objects
WHERE object_id = 2018106230;

Running that second query shows that the object lock is being held on the Sales.Invoices table of WideWorldImporters.

It Won't Always Be a Table!

Here is another example of a TAB/OBJECT wait type. This example also uses database 8, which has already been determined to be WideWorldImporters.

OBJECT: 8:1255675521:0

The object id is 125565521; this is the number to be entered into the query. This time the query will be slightly different. This query is preferred as it is smarter than the previous one because it doesn't assume the blocked object will be a table. Consider using this query instead of the first version, especially in environments where non-table objects commonly cause blocking.

USE WideWorldImporters;

SELECT SCHEMA_NAME(schema_id) TableSchema, name ObjectName, type_desc ObjectType
FROM sys.objects
WHERE object_id = 1255675521;

Notice that the blocked object this time is a stored procedure, not a table.

KEY

A wait resource of the type KEY indicates that a single row in a table is being locked. The value returned from sys.sysprocesses and sys.dm_exec_requests match in this example.

Once again, the first number after the KEY text indicates the database in which the row exists. Use the query from the prior section or this alternate query to determine the database name.

KEY: 8: 72057594049265664 (59855d342c69)

SELECT DB_NAME(8) AS DBName;

The next number is a HOBT_id. HOBT stands for Heap-Or-B-Tree. Every table that uses rowstore (which is most tables in most situations) is stored as either a heap (table with no clustered index) or B-Tree, the data structure of a clustered index.

KEY: 8:72057594049265664

Use this query to turn a HOBT_id into a table.

USE WideWorldImporters;

SELECT SCHEMA_NAME(t.schema_id) TableSchema, t.name TableName, i.name IndexName
FROM sys.partitions p
INNER JOIN sys.tables t ON p.object_id = t.object_id
LEFT OUTER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE p.hobt_id = 2057594049265664;

What is that other value at the end of the string?

KEY: 8:72057594049265664 (59855d342c69)

There is an undocumented feature that uses that value to determine the exact row of the table involved. Since it is undocumented, I will not cover it here. In my experience, there isn't much value in knowing the exact row anyway.

FIL or FILE

The wait resources FIL and FILE are synonymous. FIL will be returned by sys.sysprocesses and FILE by sys.dm_exec_requests.

FILE: 8:0

The only useful value here is the first number after the letters. In this case, the number is 8 and represents the id of the database in which the file resides.

Use one of these two methods to determine which database holds the file.

SELECT name FROM sys.databases WHERE database_id = 8;

SELECT DB_Name(8) AS DBName;

Final Thoughts

Blocking is a common occurrence on a SQL Server. When it takes so long the users begin to complain, use these tools to help diagnose why.

Next Steps

About the author

Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-05-15

Comments For This Article

Wednesday, May 17, 2023 - 8:48:07 AM - Goran Mancevski

Hello Eric,

Thank you very much for sharing this info. Here is another option that provides more info.

IF OBJECT_ID('tempdb..#Blocks') IS NOT NULL DROP TABLE #Blocks
SELECT spid,blocked,REPLACE(REPLACE(st.TEXT, CHAR(10),' '),CHAR(13),' ') batch INTO #Blocks
FROM sys.sysprocesses spr CROSS APPLY sys.dm_exec_sql_text(spr.SQL_HANDLE) st
;WITH BlockingTree(spid,blocking_spid,[level],batch)
AS (SELECT blc.spid,blc.blocked,CAST(REPLICATE('0', 4-LEN(CAST (blc.spid AS VARCHAR)))+CAST(blc.spid AS VARCHAR) AS VARCHAR(1000)) [level],blc.batch
FROM #Blocks blc WHERE (blc.blocked = 0 OR blc.blocked = SPID) AND EXISTS (SELECT * FROM #Blocks blc2 WHERE blc2.BLOCKED = blc.SPID AND blc2.BLOCKED <> blc2.SPID)
UNION ALL
SELECT blc.spid,blc.blocked,CAST(bt.[level] + RIGHT (CAST ((1000 + blc.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) [level],blc.batch
FROM #Blocks blc INNER JOIN BlockingTree bt ON blc.blocked = bt.SPID WHERE blc.blocked > 0 AND blc.blocked <> blc.SPID)
SELECT DB_NAME(spr.dbid) [Database],N''+ISNULL(REPLICATE (N'| ',LEN(LEVEL)/4-2),'')+CASE WHEN (LEN(LEVEL)/4-1)=0 THEN '' ELSE '|------ ' END
+CAST(bt.SPID AS NVARCHAR (10)) [BlockingTree],spr.lastwaittype [WaitType],spr.loginame [Login],st.[text] [SQL],
spr.cmd [Command], CASE WHEN ISNULL(spr.waitresource,' ') = ' ' THEN spr.waitresource ELSE cardionet.dbo.[GetResourceName](spr.waitresource, default) END [WaitResource],
spr.program_name [Program],spr.hostname [ComesFrom],spr.last_batch [LastBatchTime]
FROM BlockingTree bt LEFT OUTER JOIN sys.sysprocesses spr ON spr.spid = bt.spid
CROSS APPLY sys.dm_exec_sql_text(spr.SQL_HANDLE) st ORDER BY LEVEL

And the body of the function:

CREATE FUNCTION [dbo].[GetResourceName] ( @waitResource AS nvarchar(128)
, @paramName as sysname =NULL )
RETURNS nvarchar(max) --we will return t-sql
AS
BEGIN
DECLARE @blockingType AS nvarchar(20);
--type of blockin KEY,OBJECT,PAGE
DECLARE @retValue AS nvarchar(max);
DECLARE @dbId bigint;
DECLARE @blockingKey AS nvarchar(256);

SET @retValue = '';

SET @blockingType = RTRIM(SUBSTRING(@waitResource, 1, CHARINDEX(':', @waitResource) - 1));
SET @blockingKey = SUBSTRING(@waitResource, CHARINDEX(':', @waitResource) + 1, LEN(@waitResource) - CHARINDEX(':', @waitResource));
SET @dbId = SUBSTRING(@blockingKey, 1, CHARINDEX(':', @blockingKey) - 1);

IF @blockingType = 'KEY'
BEGIN

DECLARE @hobId AS bigint;
SET @hobId = RTRIM(SUBSTRING(@blockingKey, CHARINDEX(':', @blockingKey) + 1, CHARINDEX('(', @blockingKey) - CHARINDEX(':', @blockingKey) - 1));

IF @paramName IS NOT NULL
BEGIN
SET @retValue = 'SET ' + @paramName + ' = (';
END
SET @retValue = @retValue +
'SELECT sc.name + ''.'' +so.name + ''('' + si.name + '')''
FROM ' + DB_NAME(@dbId) + '.sys.partitions AS p
JOIN ' + DB_NAME(@dbId) + '.sys.objects AS so
ON p.object_id = so.object_id
JOIN ' + DB_NAME(@dbId) + '.sys.indexes AS si
ON p.index_id = si.index_id
AND p.object_id = si.object_id
JOIN ' + DB_NAME(@dbId) + '.sys.schemas AS sc
ON so.schema_id = sc.schema_id
WHERE p.hobt_id = ' + CAST(@hobId AS NVARCHAR(MAX));
IF @paramName IS NOT NULL
BEGIN
SET @retValue = @retValue + ')'
END
END
ELSE
IF @blockingType = 'OBJECT'
BEGIN
DECLARE @pos AS INT;
DECLARE @helper AS NVARCHAR(50);
DECLARE @objectId AS BIGINT;

SET @pos = CHARINDEX(':', @blockingKey) + 1;
SET @helper = SUBSTRING(@blockingKey, @pos, 100);
SET @objectId = SUBSTRING(@helper, 1, CHARINDEX(':', @helper) - 1);
--SET @retValue = N'USE ' + DB_NAME(@dbId) + CHAR(13) + CHAR(10);
SET @retValue = N'';

IF @paramName IS NOT NULL
BEGIN
SET @retValue = 'SET ' + @paramName + ' = (';
END
SET @retValue = @retValue +
'SELECT TOP 1 s.name + ''.'' + o.name
FROM ' + DB_NAME(@dbid) +' .sys.objects o
INNER JOIN ' + DB_NAME(@dbid) + '.sys.partitions p
ON p.object_id = o.object_id
INNER JOIN ' + DB_NAME(@dbid) +'.sys.schemas s
ON s.schema_id = o.schema_id
WHERE p.OBJECT_ID = ' + CAST(@objectId AS NVARCHAR(MAX));
IF @paramName IS NOT NULL
BEGIN
SET @retValue = @retValue + ')'
END

END;

ELSE
IF @blockingType = 'PAGE'
BEGIN
--PAGE 7:1:422000
DECLARE @pos1 AS INT;
DECLARE @helper1 AS NVARCHAR(50);
DECLARE @fileId AS BIGINT;
DECLARE @pageId AS BIGINT;

SET @pos1 = CHARINDEX(':', @blockingKey) + 1;
SET @helper1 = SUBSTRING(@blockingKey, @pos1, 100);
SET @fileId = SUBSTRING(@helper1, 1, CHARINDEX(':', @helper1) - 1);
SET @pageId = CAST(SUBSTRING(@helper1, CHARINDEX(':', @helper1) + 1, LEN(@helper1) - CHARINDEX(':', @helper1)) AS BIGINT)
SET @retValue = 'DECLARE @objectId as bigint

IF OBJECT_ID(''tempdb..#pagedata'') IS NOT NULL
BEGIN
DROP TABLE #pagedata
END;

CREATE TABLE #pagedata
(
ParentObject varchar(1000) NULL, Object varchar(4000) NULL, Field varchar(1000) NULL, ObjectValue varchar(max) NULL
);

' +
'DBCC traceon (3604); ' + CHAR(13) + CHAR(10) +
'SET NOCOUNT ON ;' + CHAR(13) + CHAR(10) +
'INSERT INTO #pagedata( ParentObject, Object, Field, ObjectValue )' + CHAR(13) + CHAR(10) +
'EXEC (''DBCC page (' + CAST(@dbId AS NVARCHAR(20)) + ', ' + CAST(@fileId AS NVARCHAR(10)) + ', ' + CAST(@pageId AS NVARCHAR(MAX)) + ') WITH TABLERESULTS '')
SELECT @objectId=objectvalue
FROM #pagedata
WHERE field LIKE ''Metadata: ObjectId%'';'

IF @paramName IS NOT NULL
BEGIN
SET @retValue = @retValue + 'SET ' + @paramName + ' = (';
END

SET @retValue = @retValue + 'SELECT TOP 1 s.name + ''.'' + o.name
FROM ' + DB_NAME(@dbid) +' .sys.objects o
INNER JOIN ' + DB_NAME(@dbid) + '.sys.partitions p
ON p.object_id = o.object_id
INNER JOIN ' + DB_NAME(@dbid) +'.sys.schemas s
ON s.schema_id = o.schema_id
WHERE p.OBJECT_ID = @objectId ';
IF @paramName IS NOT NULL
BEGIN
SET @retValue = @retValue + ')'
END

END;

RETURN @retvalue;
END;