Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Blocking Situation


Blocking Situation

Author
Message
SQLSACT
SQLSACT
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 2931
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47353 Visits: 44392
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


SQLSACT
SQLSACT
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 2931
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47353 Visits: 44392
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


SQLSACT
SQLSACT
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 2931
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47353 Visits: 44392
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


SQLSACT
SQLSACT
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 2931
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search