list of blocked queries RRS feed

  • Question

  •  i need a query that finds a blocked query which contains following performance metrics

    • Average Time Blocked
    • Total Time Blocked
    • Query
    • DB Name
    • Last Execution Time

    please help me.



    Tuesday, December 18, 2012 8:48 PM


All replies

  • Hello Venkat,

    Please refer to this post, I have added comment there..


    - Chintak (My Blog)

    • Marked as answer by Maggie Luo Monday, December 31, 2012 10:39 AM
    Tuesday, December 18, 2012 9:00 PM
  • Use the below query


    Thank you,

    Karthick P.K |My Site|My Scribbles |Blog Space |Twitter|My Facebook Group|


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    • Marked as answer by Maggie Luo Monday, December 31, 2012 10:39 AM
    Wednesday, December 19, 2012 4:43 AM
  • use master

    select  a.spid,
      from sysprocesses a inner join sysprocesses b   on b.spid= a.blocked and b.blocked = 0 and a.spid<> a.blocked

    --finding the text of blocked process
    SELECT    spid,
            loginame  = SUBSTRING(loginame, 1, 12),
            hostname   = SUBSTRING(hostname, 1, 12),
            blk        = CONVERT(char(3), blocked),
            dbname     = SUBSTRING(DB_NAME(sp.dbid),1,10),
            SQLStatement  = SUBSTRING(qt.text,
                                      ( CASE WHEN er.statement_end_offset = -1
                                                THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
                                                 ELSE er.statement_end_offset
                                                 END - er.statement_start_offset)/2
     FROM master.dbo.sysprocesses sp    
     LEFT JOIN sys.dm_exec_requests er        
            ON er.session_id = sp.spid    
     OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) as qt    
     WHERE spid IN ( SELECT blocked
                     FROM master.dbo.sysprocesses)    
      AND blocked = 0



    Wednesday, December 19, 2012 5:21 AM
  • sys.dm_tran_locks - this will give you more clarity on blocking issues.
    Wednesday, December 19, 2012 8:32 AM
  • Hi Venkat,

    Checkout my following article: Different techniques to identify blocking in SQL Server. This will includes several queries to troubleshoot blocking or identify blocking on SQL Server instance. 


    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Basit Farooq Wednesday, December 19, 2012 3:11 PM
    • Marked as answer by Maggie Luo Monday, December 31, 2012 10:39 AM
    Wednesday, December 19, 2012 3:11 PM