If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > SQL Server 2000 spids blocking themselves

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Location: Calgary, Alberta
Posts: 106
SQL Server 2000 spids blocking themselves

Hey guys,

I've recently noticed some strange behaviour with sql server 2000 spid's blocking themselves. The spid will appear to be blocked for short periods of time, and then the block will disappear. I'm not sure how this could occur. It started appearing around the same time as I applied SP4.

If anyone could provide any insight into this, it would be greatly appreciated.

Cheers,
-Kilka
__________________
There is nothing more helpless and irresponsible than a man in the depths of an ether binge. -HST
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: May 2002
Posts: 299
this can happen on a multi-cpu server. basically, the engine tries to create multi-thread to do work. they can step on one another.

you can force the work to be done on 1 cpu by using option(maxdop 1)
__________________
--
-oj
http://www.rac4sql.net
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Location: Calgary, Alberta
Posts: 106
thanks oj_n,

I'm guessing this hadn't appeared earlier because the load wasn't as great ? Or maybe that there is some new query that's doing some parallel processing. If that's the case, there can't be much done about it.

Thanks again oj_n,
-Kilka
__________________
There is nothing more helpless and irresponsible than a man in the depths of an ether binge. -HST
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Location: Calgary, Alberta
Posts: 106
Hey oj_n,

It just occured to me that I did indeed have parallelism in SQL server turned off and still I encounter this situation. I don't know what else to suspect now that Parallelism is out of the question. Is it possible that it's a worker thread thing ?

Thanks,
-Kilka
__________________
There is nothing more helpless and irresponsible than a man in the depths of an ether binge. -HST
Reply With Quote
  #5 (permalink)  
Old
Wage drone 24601
 
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,441
The "self-blocking" spids is new in SP4. These are not truly blocking themselves, but are waiting for a latch. You should be able to confirm this, by

Code:
select spid, blocked, waittype
from master..sysprocesses
where spid = blocked
Then looking up the waittype in this article:

http://support.microsoft.com/?kbid=822101
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Location: Calgary, Alberta
Posts: 106
Thanks MCrowley,

is this a bad thing ? What are the implications of a spid waiting on memory ? It doesn't currently seem to hurt our performance, but I'm wondering if it will in the future...

-Kilka
__________________
There is nothing more helpless and irresponsible than a man in the depths of an ether binge. -HST
Reply With Quote
  #7 (permalink)  
Old
Wage drone 24601
 
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,441
No difference, other than the fact you can see it happen, more easily, now. Microsoft probably could have come up with a better way of displaying this, but this is what we have.

EDIT: I should point out this waiting was going on for SPIDs before SP4. It just was not displayed in this fashion.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Location: Calgary, Alberta
Posts: 106
Thanks MCrowley,

your insight is appreciated.

-Kilka
__________________
There is nothing more helpless and irresponsible than a man in the depths of an ether binge. -HST
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On