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
