We have an ETL job that from time to time gets hung up.
in this case, I looked in sp_who2 and there was no blocking, and it was the only active query on the system.
Here is a dump from sysprocesses:
spid kpid blocked waittype waittime lastwaittype waitresource dbid uid cpu physical_io memusage login_time last_batch ecid open_tran status cmd
61 6276 0 0x00BB 3659611 CXPACKET 5 9 184826204 114737 2 35:53.8 35:53.8 0 2 suspended INSERT
61 6540 0 0x0000 0 SOS_SCHEDULER_YIELD 5 9 3503094 513 0 35:53.8 35:53.8 2 0 runnable INSERT
61 360 0 0x0000 0 SOS_SCHEDULER_YIELD 5 9 3377297 420 0 35:53.8 35:53.8 4 0 runnable INSERT
61 4348 0 0x0000 0 SOS_SCHEDULER_YIELD 5 9 3446657 515 0 35:53.8 35:53.8 3 0 runnable INSERT
61 5532 0 0x0000 0 SOS_SCHEDULER_YIELD 5 9 3094360 443 0 35:53.8 35:53.8 5 0 runnable INSERT
I suspect the query is going parallel and blocking itself, as all of the activity is occuring on the same spid. but I'm at a loss as to how to troubleshoot further.
any thoughts on what is happening here? or what other steps i could take to troubleshoot this if it occurs again?
Block type 00BB is CXPACKET which means that the database is waiting for another thread to complete. The other 4 threads are running as they have no waittype shown. The SOS_SCHEDULER_YIELD that is shown for the last waittype means that the SQL Server is de-scheduling the thread because it has other things to do which are easier to complete so it is working on them first.
You need to tune the query as you probably have some sort of index problem that needs to be resolved.