My spid is blocking itself 1

[Sheco]

I have a stored procedure that is blocking itself.

The code is long but very very simple. It has 20 local variables, each is assigned a different value (count or sum) by a separate simple SELECT query. By simple I mean each uses 4 or fewer tables inner joined NOLOCK. There is no WHERE, GROUP BY, HAVING, or ORDER BY clause... all criteria are part of the joins. These are no subqueries, no udf calls, cursors, temp tables or anything fancy.

I am using SQL Server 8.00.2039 (SQL 2000 SP4) on a 4 CPU box with 8gb ram.

In the Enterprise Manager, I can see the execution is spread across 5 spids, all with the same Process ID. The one with ContextID of 0 has a wait type of CXPACKET and the others with ContextID 1 thru 4 have wait type of PAGEIOLATCH_SH

I don't think it is a deadlock, but I don't really know. Can anyone point me to some documentation beyond what is covered by the BOL. All suggestions are appreciated.


PS: I read on the internet about setting the max degree of parallelism but that didnt sound like a good idea because it seems like it would prevent parallel execution across the entire server instead of on just this one procedure.

 

[mrdenny]

You can't have a deadlock with your self. With SP4 Microsoft indroduced a spid blocking it self. This simply means that you are waiting for the disk to return data to SQL Server.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Sheco]

So it might just be paging to disk?

 

[mrdenny]

Nope, SQL doesn't page to disk. SQL is reading data from the disk or writting data to the disk.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com