Post reply

SQL Locks

  • September 22, 2011 at 12:00 am

    #248348

    We have seen couple of blockings in past and need to analyse the root cause.

    Have already looked at default trace, logs, standard reqports, job etc

    No success yet

    Any other places to look which can help us drill down the root cause for blocking in past.

  • September 22, 2011 at 2:03 am

    #1384975

    This is a very open ended question.

    You need to identity the pattern of the blocking.

    Meaning the timing, process, db design, memory etc etc. To start with use service broker to log all the blocking to a table.

    Create event notification in server to log the blocking.

    I have attached how we done in our environment to capture the deadlock using service broker. Hope this helps you.

  • September 22, 2011 at 2:35 am

    #1384983

    baabhu (9/22/2011)

    Create event notification in server to log the blocking.

    I have attached how we done in our environment to capture the deadlock using service broker. Hope this helps you.

    Blocking is not the same as deadlock.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 22, 2011 at 3:13 am

    #1384996

    yea. we are talking about blocking..

    We have many monitors in place and we have been able to drill down the procs involved, and digging more if we can get the "statement"..

    So far no luck.

    This question, was more generic, in order to understand if there is any other mechanism to get this info without our own specific monitors..

    Thanks

  • September 22, 2011 at 3:41 am

    #1385009

    Query sys.dm_exec_requests cross apply sys.dm_sql_text, filter on sessions with one of the lock waits and a relatively high wait time. That'll give you the blocked statement. Take the blocking_session_id and query sys.dm_exec_requests cross apply sys.dm_sql_text and filter for session_id = that blocking session id and you'll get the session causing the blocking.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply