Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.