Query Bogged Down, sp_who2 Shows Multiple Rows w/ same SPID

Question

Post reply

Query Bogged Down, sp_who2 Shows Multiple Rows w/ same SPID

Steve Rosenbach

SSCrazy

Points: 2181
More actions
February 13, 2006 at 10:03 pm

#110984

I've run into a situation on a stored procedure where everything seems to stop. When I run sp_who2 while the sproc is running, I see multiple rows for the same SPID (the SPID in which the sproc is executing.)
The sproc seems to hang up at a point where I've populated a temporary table (to minimize rows to be processed) and then use that temporary table in a join to create a Cursor (yeah, I know, I don't like that either, but I'm constrained to use another stored procedure that needs to be called from inside a cursor)
There is a Transaction around the whole sproc - I notice that when I remove the transaction, the whole thing completes within a minute or so, but it's when I use the Transaction that things get gummed up.
Getting back to sp_Who2, none of the rows show being blocked, but they all show the same COMMAND, and some or all of them have a status of SLEEPING or DEFWAKEUP (usually, at least a few show status or RUNNABLE).
DiskIO is the same for all of these rows, and usually is stuck or barely increases as you re-run sp_Who2 - on the other hand, there are different CPUTime values
As far as I can tell, the server only has one processor - a 3GHz Xeon, and 1GB RAM.
Can anyone shed some light on what sp_Who2 is trying to tell me?
Thanks,
SteveR

Viewing 5 posts - 1 through 4 (of 4 total)

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

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

What you are seeing are multiple threads being spawned to process in parallel.

You can try providing the MAXDOP query hint to prevent use of paralellism to see if it improves the situation.

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

in addition to PW 's reply ...

is the create table for the temptb also within this transaction ?

I've read about this situation might have some issues with tembDb blocking.

I cannot recall the article right now, but when I find it again I'll provide the link.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Steve Rosenbach SSCrazy Points: 2181 More actions · Answer 3

THANKS!!! to both PW and alzdba for your suggestions! (and overnight, while I was sleeping, no less! 🙂 )

I tried two things that both worked:

1. I created the temp table outside of the transaction, and the sproc ran fine. This did bother me though - even though this sproc will be run at night, I was still worried about a change in data between the time the temp table was created and when it was used, some 15-30 seconds or so later.

2. Then I rewrote the SELECT for the cursor that had used the temp table, substituting a derived table in place of the temp table. This also ran fine, and everything is now inside a transaction.

We've written plenty of stored procedures before where we've created temporary tables inside a transaction and hadn't yet run into this problem.

alzdba - I would be really interested in that article if you can find it.

BTW - PW - I tried the OPTION (MAXDOP 1) - I could see that it did indeed eliminate the parallel threads - but it turned out that wasn't the problem - the process still hung up at that point (DiskIO just wasn't increasing). But thank you for the suggestion!

Best regards,

SteveR

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

It's not exactly what I was searching for, but maybe I'll comeup with it later :

- http://www.sql-server-performance.com/reducing_locks.asp

- http://www.sql-server-performance.com/sf_block_prevention.asp

- http://support.microsoft.com/kb/q193095/