Self Blocking SPIDS

Question

Post reply

Self Blocking SPIDS

Sean Brown

SSC-Addicted

Points: 449
More actions
August 10, 2005 at 10:35 am

#110073

Hello,
Has anyone ever seen cases where when you run sp_who it indicates that a SPID is being blocked by itself? In the past 3 weeks I have started to see that happen, and I have never seen that before. I spoke to out DBA and he has not seen that either. I have seen this happen with both stored procedures and Ad-Hoc queries. It does not appear that the SPID is actually being blocked, since the query complete and whatever the query is supposed to do gets done. This is making it harder to determine real instances of blocking and sometimes results in processes being killed in order to stop blocking, when blocking may not have gone on. If anyone has any ideas about what may be happening here I would appreciate it.

Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Do you have hyperthreading processors or multiple cpus on the server?

Sean Brown SSC-Addicted Points: 449 More actions · Answer 2

I don't know for sure, if that mught be the issue I can ask the DBA. But the issue only came up in the last few weeks. The only hardware changes we have had in months was adding memory to the servers.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Maybe it's only new code that is exposing that. I've seen cases where paralellism was blocking itself... can't explain why though.

Yukas Mr or Mrs. 500 Points: 521 More actions · Answer 4

>> in the last few weeks

I suppose it started with installing SP4. Ask your DBA.

Under my observations, the first difference between SP3 and SP4 is the way how SQLServer displays processes in master.dbo.sysprocesses.

"Self-blocking" means that process not blocked but waiting resource. This is my opinion, I have not found any explanations about this on microsoft.com

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 5

This issue has been raised on http://www.SQLTeam.com and relates to a bug in SP4, which has been acknowledged by Paul Randal of the Microsoft SQL Development team. Search there for more information on the problem and possible resolutions.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 6

I seem to recollect seeing an article from microsoft which stated this was a change in reporting for SP4 - I'm sure if you search technet you will find this article.

If you have more than 1 proc it is quite possible to have waits upon threads and threads causing waits - these can often be seen as cxpacket locks. Try using a maxdop hint as a test to see if it's actually a real problem ( and if it improves things ) I've just actually tested a maxdop hint in a proc which speeded up execution many times - the code is/was basically good but the base table has grown to over 200 million rows so the optimiser was deciding to use parallelism - which in this case was a bad idea !!

this is the article - I don't have the ms path

SQL Server Developer Center Detecting and Resolving Stalled and Stuck I-O Issues in SQL Server 2000 SP 4.htm

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Sean Brown SSC-Addicted Points: 449 More actions · Answer 7

I do seem to remember an e-mail from the DBA about installing service pack 4 a few weeks ago. I will look into it.

Thanks for all the great responses everyone!

-Sean

homebrew01 SSC Guru Points: 55539 More actions · Answer 8

I noticed quite a lot of this today as well, and wonder if anyone else has fresh thoughts on this.

We're on SP4, by the way

EDIT: I just found this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;906344

PW-201837 SSC-Insane Points: 20805 More actions · Answer 9

This started with SP4. I've never seen it called a "bug".

The best explanation I've seen so far is here:

http://groups.google.com/group/microsoft.public.sqlserver.server/msg/b86e343e513ab281?hl=en

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 10

I think if you read the readme for sp4 you'll find it's considered an enhancement of reporting. I just happened to scan though it looking for something else today .. and then up pops a mail from this thread!!!

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Zahid Hasan SSC Veteran Points: 258 More actions · Answer 11

I used to think that this self block related to sql server 2000 sp4 but i have seen that on sp3 as well. Sometimes this last for a short time. sometimes it last very long time. There is some explanation on Microsoft support site but its not very striaght forward.