Understanding SQL Server Blocking

By:   |   Updated: 2010-04-06   |   Comments   |   Related: 1 | 2 | 3 | 4 | More > Locking and Blocking


In my previous article, I provided you with a script that presented the current locks being held in the active database.  While this script also provided limited information on blocking that was occurring, it did not give a fully-informative insight on that matter.  I alluded to a different solution for reviewing blocking and this is the subject of this article. 

For those of you who've not yet read the Understanding SQL Server Locking tip I suggest doing so as it gives a high-level view of sys.dm_tran_locks, locking, and the objects we'll be discussing in this tip.


Blocking is the logical outcome of locks being issued against various objects in a database.  If a request is made against an object with a lock issued against it, then a block occurs. 

For example...

Request A is attempting to insert a row in table X.  Subsequently, before the Request A completes, Request B is submitted to update table X.  If the row affected by Request A is also affected by Request B then a block is encountered because, among other things, the RID (row id) for the row affected by Request A has an exclusive (X) lock issued against it for the duration of the transaction involved in Request A.

This behavior will be used as the basis of observing blocking via the script this article is centered-on.  The behavior is manifested via two scripts:

INSERT INTO dbo.Categories([CategoryName], [Description])
VALUES ('Foo', 'Description FOR Foo')

Notice that I've left the transaction in a running state.  This is so we can examine the blocking behavior that will be caused when we issue the subsequent update script that follows:

SET [Description] = 'Foo'
WHERE [CategoryName] = 'Foo';

So how do we observe the blocking that is currently being experience as a result of these two open transactions?  Well in the case of using the Dynamic Management Views (DMVs) which is the subject of this article, we query among other objects the sys.dm_trans_locks DMV as well as a few select other system objects using the following query:

--View Blocking in Current Database
--Author: Timothy Ford
SELECT DTL.resource_type
WHEN DTL.resource_type IN ('DATABASE', 'FILE', 'METADATA') THEN DTL.resource_type 
WHEN DTL.resource_type = 'OBJECT' THEN OBJECT_NAME(DTL.resource_associated_entity_id
WHEN DTL.resource_type IN ('KEY', 'PAGE', 'RID') THEN  

FROM sys.partitions 
WHERE sys.partitions.hobt_id =  

ELSE 'Unidentified' 
END AS requested_object_name, DTL.request_mode, DTL.request_status,   
DOWT.wait_duration_ms, DOWT.wait_type, DOWT.session_id AS [blocked_session_id]
sp_blocked.[loginame] AS [blocked_user], DEST_blocked.[text] AS [blocked_command],
DOWT.blocking_session_id, sp_blocking.[loginame] AS [blocking_user]
DEST_blocking.[text] AS [blocking_command], DOWT.resource_description    
FROM sys.dm_tran_locks DTL 
INNER JOIN sys.dm_os_waiting_tasks DOWT  
ON DTL.lock_owner_address = DOWT.resource_address  
INNER JOIN sys.sysprocesses sp_blocked 
ON DOWT.[session_id] = sp_blocked.[spid]
INNER JOIN sys.sysprocesses sp_blocking 
ON DOWT.[blocking_session_id] = sp_blocking.[spid]
CROSS APPLY sys.[dm_exec_sql_text](sp_blocked.[sql_handle]) AS DEST_blocked
CROSS APPLY sys.[dm_exec_sql_text](sp_blocking.[sql_handle]) AS DEST_blocking
WHERE DTL.[resource_database_id] = DB_ID()

The script output is conveniently divided into two sections (and I've split the screen shots of the output along those lines): information pertaining to the blocked request and information about the blocking request.

information pertaining to the blocked request

information about the blocking request

Metadata about the blocked request includes the name of the object incurring the block, the lock attempting to be granted to the blocked request (in this case an U or Update lock), the status of the request (WAIT), the type of wait being encountered and its duration, as well as the session_id, command text, and login for the blocked request.  Likewise, the session_id, login_id, command text, and resource_description of the blocking transaction is provided.  All items are fairly self-explanatory, with the exception of resource_description.  Its value is comprised of the various IDs associated with the objects involved in the lock at various granularities. 

Let's look at the full value of this column for the query we've executed:

Let's look at the full value of this column for the query we've executed

The value of this field denotes that file 1 (the primary data file for this database) which is identified by the database_id (or dbid) of 8 is affected.  Furthermore, the lock id is provided as is the object_id that corresponds to the object in question for the lock.  In the case of an RID lock, this is the partition_id of the table in question.

If I was to rollback my INSERT_FOO transaction or commit it you'll see the state of blocking for the active database I've been working with is greatly diminished; matter-of-fact.  There is none to report.

Next Steps

get scripts

next tip button

About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

View all my tips

Article Last Updated: 2010-04-06

Comments For This Article


get free sql tips
agree to terms