locked
list of blocked queries RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

     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.

    thanks,

    venkat

    Tuesday, December 18, 2012 8:48 PM

Answers

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello Venkat,

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

    http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/dba3f480-d1e9-471a-bd37-1a948e3a1347

    - Chintak (My Blog)

    • Marked as answer by Maggie Luo Monday, December 31, 2012 10:39 AM
    Tuesday, December 18, 2012 9:00 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Use the below query

    http://mssqlwiki.com/2010/11/24/script-to-get-current-blocking-tree-with-wait-types/

    Thank you,

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

    www.Mssqlwiki.com

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    use master

    select  a.spid,
     a.blocked,
      b.spid,
      b.blocked   
      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,
            sp.status,
            loginame  = SUBSTRING(loginame, 1, 12),
            hostname   = SUBSTRING(hostname, 1, 12),
            blk        = CONVERT(char(3), blocked),
            open_tran,
            dbname     = SUBSTRING(DB_NAME(sp.dbid),1,10),
            cmd,
            waittype,
            waittime,
            last_batch,
            SQLStatement  = SUBSTRING(qt.text,
                                      er.statement_start_offset/2,
                                      ( 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

    Goodluck

    kumar

    Wednesday, December 19, 2012 5:21 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    sys.dm_tran_locks - this will give you more clarity on blocking issues.
    Wednesday, December 19, 2012 8:32 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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. 

    Regards,

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

    http://basitaalishan.com

    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