/* */ Deadlock and blocking in SQL Server - Part 2 |SQL Works

Wednesday, October 17, 2012

Deadlock and blocking in SQL Server - Part 2

With SQL Server 2005 a set of dynamic management views were introduced to allow you better visibility into what exactly was happening within the instances of SQL Server you manage or use. The below script takes the outdated sys.sysprocesses and fn_get_sql functions (which still work but are deprecated) and replaces them with new DMVs sys.dm_exec_requests and sys.dm_exec_sql_text.
Additionally it more narrowly defines the SQL statement being executed by both the blockee and blocker by trimming down the full text of the script or procedure and only returning the actual code currently executing. This is done by using the statement_start and statement_end offset values in the sys.dm_exec_requests view.
There is a lot more information available on what currently executing statements are doing within your instance, but this script will get you some of the most important information about who is blocking who quickly. I will expand on this again in the near future with more details about what resources are being contested, and other metrics.

/*
   Created: October 2012
   Author: SQLWorks blog
   URL: http://www.SQLWorks.blogspot.com
   Changelog:
   10/17/2012 - replaced sysprocesses with new DMV sys.dm_exec_requests
              - replaced fn_get_sql with new DMV dm_exec_sql_text
              - Used SUBSTRING and statement_offet values to determine exact statement that               is currently executing
              - instead of returning entire command or proceure
*/

DECLARE @sql_handle TABLE(blockee_spid INTblockee_cmd VARCHAR(MAX), blocker_spid INTblocker_cmd VARCHAR(MAX))
INSERT INTO @sql_handle
   
SELECT sp1.session_id AS blockee_spid,

           
SUBSTRING(blockee_cmd.TEXT,
               
CASE WHEN sp1.statement_start_offset >= THEN sp1.statement_start_offset
                   
ELSE END,
               
CASE WHEN (sp1.statement_end_offset-sp1.statement_start_offset) >= THEN                      (SELECT LEN(TEXTFROM sys.dm_exec_sql_text(sp1.sql_handle))
                   
ELSE (sp1.statement_end_offset-sp1.statement_start_offsetEND),

           
sp1.blocking_session_id AS blocker_spid,

           
SUBSTRING(blocker_cmd.TEXT,
               
CASE WHEN sp2.statement_start_offset >= THEN sp2.statement_start_offset
                   
ELSE END,
               
CASE WHEN (sp2.statement_end_offset-sp2.statement_start_offset)<= THEN          (SELECT LEN(TEXTFROM sys.dm_exec_sql_text(sp2.sql_handle))
                   
ELSE (sp2.statement_end_offset-sp2.statement_start_offsetENDAS blocker_cmd

       
FROM sys.dm_exec_requests sp1
           
JOIN sys.dm_exec_requests sp2
           
ON sp1.blocking_session_id sp2.session_id
       
CROSS APPLY sys.dm_exec_sql_text((sp1.sql_handle)) AS blockee_cmd
       
CROSS APPLY sys.dm_exec_sql_text((sp2.sql_handle)) AS blocker_cmd
       
WHERE sp1.blocking_session_id <> 0

SELECT 
*FROM @sql_handle

Using the same test data from Part 1 of this post, I added a second simple SELECT query that would be blocked by the update loop and then ran the above statement. The results show the way that the statements are blocked by each other.


Blocking script results multiple spid blocked TSQL SQL Server

What you can see here is that spid 52 (the update loop) is blocking spid 56 (the first SELECT) and then spid 56 is blocking spid 53 (the second SELECT). These results are a bit hard to look at, so in the next post I will modify how they are displayed and get a more usable result set returned.

Go back to Part 1 - blocking and deadlocks


If you found this useful please click the +1 to share - its FREE!