When a data resource is required by a query locks are acquired to achieve concurrency. When two processes want to access the same data resource, depending on your isolation level and access mode (read, update, delete) one process may need to wait for the other resulting in a blocking situation. It is conceivable to then have a chain of blockers, process A is blocking process B, B in turn is blocking C and so on down the track.

Fortunately for us, the sys.dm_exec_requests Dynamic Management View (DMV) exposes a column telling us whether a process is currently waiting for access to resources held by another process, and thankfully what that process is. This column is called Blocking_Session_ID and can be useful in determining which session is at the top of the heirarchy - who is causing the actual problems.

Scenario Setup

For this example we are going to set up a chain of three blocking processes - so you will need three connections to your database for this and a fourth to look at the blocking.

Window 1:

In window 1 we create a database and set it up with two tables, then start up a transaction inserting into one of the tables leaving the transaction uncommitted.

 Create Database Blocking_Test
 Go
 Use Blocking_Test
 Go
 Create Table Test_Blocking (Record_Key int not null primary key, Record_Text nvarchar(50))
 Go
 Create Table Test_Blocking_2 (Record_Key int not null primary key, Record_Text nvarchar(50))
 Go
 Begin Transaction
  Insert Into Test_Blocking Values (1,'First Record'),(2,'Second Record')
 -- Commit Transaction

Window 2:

Using an uncommitted transaction in window 2 first insert a row into the second of the two tables, then attempt an insert into the first table which conflicts with what you have done on window 1.

 Use Blocking_Test
 Go
 Begin Transaction
  Insert Into Test_Blocking_2 Values (1,'First Record'),(2,'Second Record')
  Insert Into Test_Blocking Values (1,'My Value')
 -- Commit Transaction

Window 3:

Finally in window 3 attempt to insert a conflicting row into the second table.

 Use Blocking_Test
 Go
 Insert Into Test_Blocking_2 Values (1,'Test')

We have ended up with a situation where process 1 has written to table Test_Blocking. Process 2 has succesfully written to table Test_Blocking_2 and is then attempting to write a record to table Test_Blocking, which conflicts with the key value that process 1 has inserted. Window 3 is then attempting to add a record to table Test_Blocking_2.

Examining the blocks

In an ideal world we would have very few active processes on the server and we could look straight to the requests DMV - in your fourth window execute the following:

Select Session_ID,Blocking_Session_ID
 From Sys.DM_Exec_Requests
 Where Session_ID > 50
 And Blocking_Session_Id <> 0

You should get results similar to this (session ID's will differ obviously)

Session_ID Blocking_Session_ID

53  52

55  53

This is telling us that session 55 is waiting for session 53 to release some locks, and session 53 is waiting for session 52 to release some locks, but there is no mention of session 52 waiting for anyone. Session 52 must be the cause of our problems. In reality though it's not that simple. You might have hundreds of processes connected and querying on your server and a blocking chain with dozens of levels. If you were to look through this table manually it might take you longer than your client wants to wait for their system to get back to normal. So what do we do?

Simple - we look for a blocking session ID which does not correspond to a record in the requests table that has a non-zero blocking session ID. We can then use this and other DMV's to get as many details about the offending process as we want.

I use this particular script just about daily to look at blocking situations on our servers. It uses the sys.dm_exec_sessions DMV to get the session information (login, hostname etc) along with some details from the sys.dm_exec_requests DMV to get details of the process doing the blocking. Further the sys.dm_exec_sql_text dynamic management function (DMF) is used to get the actual query text of the query that is presently running (if there is one).

The final column in the output uses a case statement to present some feedback as to what the process is currently doing. This can aid in determining why the blocking situation is happening.

 Select  DES.Session_ID As [Root Blocking Session ID],
    DER.Status As [Blocking Session Request Status],
    DES.Login_Time As [Blocking Session Login Time],
    DES.Login_Name As [Blocking Session Login Name],
    DES.Host_Name As [Blocking Session Host Name],
    Coalesce(DER.Start_Time,DES.Last_Request_Start_Time) As [Request Start Time],
    Case
     When DES.Last_Request_End_Time >= DES.Last_Request_Start_Time Then DES.Last_Request_End_Time
     Else Null
    End As [Request End Time],
    Substring(Text,DER.Statement_Start_Offset/2,
        Case
         When DER.Statement_End_Offset = -1 Then DataLength(Text)
         Else DER.Statement_End_Offset/2
        End) As [Executing Command],
    Case
     When DER.Session_ID Is Null Then 'Blocking session does not have an open request and may be due to an uncommitted transaction.'
     When DER.Wait_Type Is Not Null Then 'Blocking session is currently experiencing a '+DER.Wait_Type+' wait.'
     When DER.Status = 'Runnable' Then 'Blocking session is currently waiting for CPU time.'
     When DER.Status = 'Suspended' Then 'Blocking session has been suspended by the scheduler.'
     Else 'Blocking session is currently in a '+DER.Status+' status.'
    End As [Blocking Notes]
 From  Sys.DM_Exec_Sessions DES (READUNCOMMITTED)
 Left Join Sys.DM_Exec_Requests DER (READUNCOMMITTED)
   On DER.Session_ID = DES.Session_ID
 Outer Apply Sys.DM_Exec_Sql_Text(DER.Sql_Handle)
 Where  DES.Session_ID In (
         Select Blocking_Session_ID
         From Sys.DM_Exec_Requests (READUNCOMMITTED)
         Where Blocking_Session_ID <> 0
          And Blocking_Session_ID Not In (
                   Select session_id
                   From Sys.DM_Exec_Requests (READUNCOMMITTED)
                   Where Blocking_Session_ID <> 0
                  )
         )

The results of this query in this example are:

Root Blocking Session ID Blocking Session Request Status Blocking Session Login Time Blocking Session Login Name Blocking Session Host Name Request Start Time Request End Time Executing Command Blocking Notes

52 NULL 2013-09-05 08:46:00.623 sa SERVER01 2013-09-05 08:50:28.080 2013-09-05 08:51:18.083 NULL Blocking session does not have an open request and may be due to an uncommitted transaction.

This tells us that session 52 is at the root of the blocking problems, that the process is not actively running any requests and therefore may be a result of someone leaving an uncommitted transaction lying around.

We can go back to window 1 and have it do something - Waitfor Delay '00:00:50.000' - do a large time-consuming table scan or jump into a runaway CLR routine and run the above script again in window 4 to see what changes.

Root Blocking Session ID Blocking Session Request Status Blocking Session Login Time Blocking Session Login Name Blocking Session Host Name Request Start Time Request End Time Executing Command Blocking Notes

52 suspended 2013-09-05 08:46:00.623 sa TITANDEV17 2013-09-05 09:31:08.300 NULL Waitfor Delay '00:00:50.000' Blocking session is currently experiencing a WAITFOR wait.

Cleanup

Execute a Rollback Transaction in windows 1 and 2; close windows 2, 3 and 4. Then you can drop the database using window 1.

Use master
Go
Drop Database Blocking_Test
Go

Conclusion

The DMV's exposed by SQL Server 2005 onwards can be powerful tools to identify the source of blocking on a server. I have the above script as part of a stored procedure in the master database so I can call it from anywhere at anytime and it has proved to be a highly valuable tool to me over the last few years.