Post reply

Blocking Situation

  • December 9, 2012 at 11:05 pm

    #267898

    Hi All

    I was dealing with an issue that I encountered a few days ago

    I was doing my regular Index rebuilds (Online) on one of my large tables. As the rebuild was running I noticed excessive blocking and it wouldn't go away.

    I found that the head of the blocking chain was spid 88, my Index rebuild operation was running under spid 236.

    I stopped the Index rebuild operation and all the blocking seemed to go away.

    My question is, if SPID 88 was the head of the blocking chain, why would stopping my reindexing (running under spid 236) clear up the blocking?

    Any Ideas?

    Thanks

  • December 10, 2012 at 1:33 am

    #1566925

    What was session 88 doing?

    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

  • December 10, 2012 at 2:06 am

    #1566930

    GilaMonster (12/10/2012)

    What was session 88 doing?

    According to sp_who2, a select statement from the same table I was rebuilding on. It wasn't active though, it was in a sleeping state

    I checked the isolation level of session 88 to check if it's maybe running under a strict isolation level, it wasn't. It was running readcommitted

    Thanks

  • December 10, 2012 at 2:15 am

    #1566933

    What locks was it holding?

    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

  • December 10, 2012 at 2:21 am

    #1566936

    GilaMonster (12/10/2012)

    What locks was it holding?

    I check sp_lock 88 when it was happening and it was holding a mixture of IX, IS key and page locks. If memory serves, sp_lock 88 returned roughly 120 rows

    If it was holding a table lock, would I see all the lower level locks being returned by the sp_lock procedure and the table lock or will it just show a table lock?

    Thanks

  • December 10, 2012 at 2:29 am

    #1566939

    You see all the locks that are held.

    If the select's session was holding an IS (from an open transaction and previous statements), that's enough to block the index rebuild. Everything else that wanted locks would have to queue up behind the index rebuild. When that's killed all the locks queued up behind the index rebuild would then be granted, because the locks that the select's session holds won't block them.

    Why are you still using sp_who and sp_lock? They're both SQL 2000 (and before) procedures

    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

  • December 10, 2012 at 2:36 am

    #1566940

    GilaMonster (12/10/2012)

    You see all the locks that are held.

    If the select's session was holding an IS (from an open transaction and previous statements), that's enough to block the index rebuild. Everything else that wanted locks would have to queue up behind the index rebuild. When that's killed all the locks queued up behind the index rebuild would then be granted, because the locks that the select's session holds won't block them.

    Why are you still using sp_who and sp_lock? They're both SQL 2000 (and before) procedures

    Thanks

    Why are you still using sp_who and sp_lock? They're both SQL 2000 (and before) procedures

    No real justification for this, I need to get into the habit of using DMV's.

    Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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