Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Blocking Situation Expand / Collapse
Author
Message
Posted Sunday, December 9, 2012 11:05 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:03 AM
Points: 1,380, Visits: 2,708
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
Post #1394460
Posted Monday, December 10, 2012 1:33 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 40,614, Visits: 37,079
What was session 88 doing?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1394479
Posted Monday, December 10, 2012 2:06 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:03 AM
Points: 1,380, Visits: 2,708
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
Post #1394490
Posted Monday, December 10, 2012 2:15 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 40,614, Visits: 37,079
What locks was it holding?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1394494
Posted Monday, December 10, 2012 2:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:03 AM
Points: 1,380, Visits: 2,708
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
Post #1394497
Posted Monday, December 10, 2012 2:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 40,614, Visits: 37,079
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 2008, MVP
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

Post #1394500
Posted Monday, December 10, 2012 2:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:03 AM
Points: 1,380, Visits: 2,708
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
Post #1394501
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse