SQL SERVER – Quickest Way to Identify Blocking Query and Resolution – Dirty Solution

As the title suggests, this is quite a dirty solution; it’s not as elegant as you expect.

The Story:

I got a phone call at night (11 PM) from one of my old friends, requesting a hand. He asked me if I could help him with a very strange situation. He was facing a condition where he was not able to delete data from a table. He already tried to TRUNCATE, DELETE and DROP on the table, but still no luck. I demanded him to let me access it; however, he had to say “No” due to security reasons. Even though he really wanted my help, he was not authorized to even let me glance at his screen. After doing some background checks about the problem, I realized that he had open transactions somewhere, and this finally led to the solution of the issue. In his case, there was a transaction which was unnecessarily open, and was actually open for a long time now. It was safe for him to kill the transaction, so he killed it and everything moved on.

However, killing transactions can be too damaging to your server, so do not use this method; there are other (and safer) ways.

Here is the script we used to identify the blocking query.

SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO

The query above returned us the following results:

In our case, we killed the blocking_session_id after carefully looking at the BlockingText; it was found to be not necessary at all. We killed the session using the following command:

KILL 52

As mentioned earlier, if you kill something important on your production server, there’s a great possibility that you’ll face some serious integrity issues, so I there’s no way I advise use this method. As the title goes, this is a dirty solution so you must utilize this only if you are confident.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

28 thoughts on “SQL SERVER – Quickest Way to Identify Blocking Query and Resolution – Dirty Solution

  1. I’m trying to run the query on SQL Server 2008 and it is breakingon the CROSS APPLY line in the predicate – not sure what this is

  2. Thanks.

    Not sure if I got this from you too, but our dirty solution for the same problem is finding which connection has been running for long time.

    Probably this only works well with small, simple and low traffic implementations.

    SELECT sqltext.TEXT,
    req.session_id,
    req.status,
    req.command,
    req.cpu_time,
    req.total_elapsed_time
    FROM sys.dm_exec_requests req
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

  3. If this is once a blue moon blocking, you might kill it and call victory. However, I suggest readers first understand the implications.

    1) If this is a web app, when you kill it, the user on the other end of the world might re-submit again after seeing execution unsuccessful. Blocking occurs again. You are tracing tail.

    2) If the code has fundamental issues, you need to identify the code issue and point out to developers to fix. Kill it in reaal-time won’t do anything good in the long-run.

    Please visit my SQLSaturday#57 presentation which covers blocking.

    If you are killing the same job, same code execution, you better stop and find out what is wrong with that code.

  4. i get the session blocking because one Procedure is blocking to other Procedure.
    After looking into the procedures we found that the select query in both the procedures uses the same function and the joins are on same tables in both procedures.
    These two procedures were executing at same time and hence the blocking issue occurred.
    Can u tell me the possible solution on this issue ?

  5. Thanks a bunch, that saved me a fair bit of time. How many years have you spent learning Microsoft SQL? You so are an internet high-and-mighty at it!

    Thanks again, Garry.

  6. Hi Pinal,

    I was testing your query and found that it didnt give me the results that I was expecting.
    To reproduce, I start a transaction and did an insert. Now in another query window i go and do a select on the same table.
    Now if I run the query I am not able to see any results,
    Due to the fact that in sys.dm_os_waiting_tasks we dont see the spid which is actually blocking. It is the same case even if you do query sys.dm_exec_requests.

  7. This will gives us the full text (Eg,. For an SP it will show us the full code) is there any way to get the only statement causing blocking along with the SP name?…

  8. Hi,
    I am using following method for finding the blocked processes.

    SELECT spid FROM master..SYSPROCESSES WHERE blocked0
    Let’s say 56 is the spid blocking

    DBCC INPUTBUFFER(56) — Will give you the Event Info

    KILL 56

    • But deepkt if any job running for above spid56 then if u kill means
      that job will fail and create problems..so before killing any process just look at what it is doing and decide which one to kill either 52 or 56..

      thanks

    • hi,
      i usually do the same thing mentioned above when the situations happens.
      But i want to automatically kill the procees if any long running queries occur.
      What can i do??/

  9. Try this…
    sp_who2 active>>> will result with the spids, and other details and has a column showing the blocking process if any.
    then use DBCC inputbuffer ()
    if not a dml (to prevent data inconsistency, we should avoid killing a insert, update or delete), use kill
    This will kill the blocking process and the resource/object will be realeased hence resolving the blocking

  10. Hi Pinal,

    The above query for identifying blocking is working but this information is dynamic, but what if we are getting block somewhere around midnight and get a mail in the morning saying there was a blocking at midnight please check.
    How can we record blocking history is my question.

    Thanks
    Karthik

  11. Hi Pinal,

    Good Morning !!!

    i am supporting SQL server 2005 .There are around 100 SQL jobs which used to be scheduled to run at its time on daily, weekly, weekday and Monthly basis.

    But, i would like to bring to your notice that there are some SQL jobs which are taking more time to execute than expected.

    we would like to fix this issue asap to ensure that all the SQL jobs will be running on its schedule timings without long execution.

  12. Hi Pinal,
    In our organization we are running an ERP Software. This software was developed using ASP.NET as Front End and SQL Server 2005 as Back end… When more number of users accessing at the same time means we are facing slowness in Execution… Some times SQL server gets Hang… What we have to do to overcome this problem… Pls do the needful

  13. I found the INNER JOIN to sys.partitions is problematic. If the block is on an object, then it needs to go against sysobjects. Seems we need to case out a join if resource_type = object then sys.sysobjects …

  14. Pingback: SQL SERVER – Weekly Series – Memory Lane – #050 | Journey to SQL Authority with Pinal Dave

  15. Pingback: SQL SERVER – Activity Monitor to Identify Blocking – Find Expensive Queries | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s