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 INT, blockee_cmd VARCHAR(MAX), blocker_spid INT, blocker_cmd VARCHAR(MAX))
INSERT INTO @sql_handle
   SELECT sp1.session_id AS blockee_spid,

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

           sp1.blocking_session_id AS blocker_spid,

           SUBSTRING(blocker_cmd.TEXT,
               CASE WHEN sp2.statement_start_offset >= 0 THEN sp2.statement_start_offset
                   ELSE 0 END,
               CASE WHEN (sp2.statement_end_offset-sp2.statement_start_offset)<= 0 THEN          (SELECT LEN(TEXT) FROM sys.dm_exec_sql_text(sp2.sql_handle))
                   ELSE (sp2.statement_end_offset-sp2.statement_start_offset) END) AS 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.

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!

Deadlocks and blocking in SQL Server

As a DBA you are probably faced with the issue of users blocking each other's queries in your databases and potentially deadlocks between queries as well. Here is how I identify and resolve those issues. This method is simple and quick and probably all you need for most situations, there are much more elaborate ways of tracing and troubleshooting these, but for user queries and in-the-moment identification of issues, this way almost always works.

First, the difference between blocking or contention and a deadlock in simple terms, blocking is when one spid or query blocks another from using a resource or placing the lock it needs on said resource, and a deadlock is when two users or queries each holds a resource the other needs next and neither will let go, so the engine chooses a 'victim' and ends the standoff by killing one query. Again this is really simplified but its all you need to know to get started. Now how do we identify when each scenario is happening?

Lets set up some test data first:

/* Created: October 2012
   Author: SQLWorks Consulting blog
   URL: http://www.SQLWorks.blogspot.com
*/
CREATE TABLE [dbo].[EmployeePerf](
   [EmployeeID] [int] NOT NULL,
   [EmployeeName] [varchar](25) NOT NULL,
   [Sales] [int] NULL,
   [TimePeriod] [int] NULL
)
-- test data
INSERT INTO dbo.EmployeePerf VALUES(1,'Tony',100,'1')
INSERT INTO dbo.EmployeePerf VALUES(1,'Tony',300,'2')
INSERT INTO dbo.EmployeePerf VALUES(1,'Tony',200,'3')
INSERT INTO dbo.EmployeePerf VALUES(1,'Tony',150,'4')  

This is just a table I had set up already on my server to test something else, so I am reusing it, the contents of the table are not really relevant to the discussion on database contention.

So now we need two queries, one to block the other for our testing:
-- Looping update to create a lock for demonstration
DECLARE @sales INT = 100
BEGIN TRAN
WHILE @sales < 500000
BEGIN
   UPDATE [dbo].[EmployeePerf]
   SET sales = @sales
       WHERE Employeename = 'tony' AND timeperiod = 1

   SET @sales = @sales +1
CONTINUE
END

COMMIT TRAN

and in a second window, you will run this concurrently and it will be blocked by the first:

-- select statement that will be blocked by above update statement for demonstration
-- Run this in a different query window!
SELECT *
   FROM dbo.EmployeePerf

So now that we have blocking occurring, what do we do about it? Well first I always like to know who is blocking who, and the commands being attempted by each party. To find that out there are several methods, here is the one I use in SQL Server 2012:

DECLARE @sql_handle TABLE(blockee_spid INT, blockee_cmd VARCHAR(MAX), blocker_spid INT, blocker_cmd VARCHAR(MAX))INSERT INTO @sql_handle
   SELECT sp1.spid AS blockee_spid, blockee_cmd.TEXT AS blockee_cmd, sp1.blocked AS blocker_spid, blocker_cmd.TEXT AS blocker_cmd
       FROM sys.sysprocesses sp1
           JOIN sys.sysprocesses sp2
           ON sp1.blocked = sp2.spid
       CROSS APPLY sys.dm_exec_sql_text((sp1.sql_handle)) AS blockee_cmd --updated
       CROSS APPLY sys.dm_exec_sql_text((sp2.sql_handle)) AS blocker_cmd --updated
       WHERE sp1.blocked <> 0

SELECT *FROM @sql_handle  

This uses the sys.sysprocesses system view to see what is blocking and being blocked and then calls the fn_get_sql function for each to determine what code is being run. If you run the two queries above, then run this script in a third window you will get the below results:

As you can see, it provides the spid of the blocker and blockee, as well as the commands being run by each. There is so much more to delve into on this subject but this will get you moving, and give you a quick way to answer the recurring user question we all love "Who is blocking my query?!?!"


-- EDIT - updated with suggestions from @AaronBertrand of SQLBlog.com
-- changed fn_get_sql to sys.dm_exec_sql_text

-- EDIT - changed formatting of code so comment lines didn't run over code
  -- EDIT - again fixed weird formatting in the code snippets

coming tomorrow, Part 2 - new version using dm views

If you found this useful, click the +1 or Twitter buttons to share -- thanks

Error 1406 installing Office Pro Plus 2010

After a recent computer upgrade I was faced with re-installing the Office 2010 Pro Plus suite, which I had the discs for and assumed would be a no brainer. Upon happily inserting the CD and expecting to hit 'GO' and grab a snickers I was suddenly faced with this:

ERROR 1406: Setup cannot write the value Assembly to the registry key

After some reading about how I did not have sufficient permissions to alter my registry, and knowing this was not true, I tried a theory and disabled my anti-virus software (AdAware AntiVirus free edition) and the installation worked without an issue. I have also read references to WebRoot SpySweeper blocking registry changes as well, so if you are facing this same problem, before you go changing registry permissions, try disabling your anti-virus software, it may just work. Don't forget to turn it back on...

If you found this useful, please hit the +1, it's free!