Environment:

• SQL Server 2000 sp3
• 8 CPUs
• Auto Statistics option is ON
• Simple Recovery Mode

Issue:

• Running a batch of INSERT statements that has been running for a very long time during development of a migration tool

Some diagnostic

• SELECT * FROM sysprocesses WHERE spid = 53

spid   kpid   blocked waittype waittime    lastwaittype     waitresource  ecid  
53     2104   0       0x0000   0           PAGEIOLATCH_EX   13:1:6938984    0
53     2828   0       0x0208   16          CXPACKET                                  1
53     2680   0       0x0208   16          CXPACKET                                  2
53     704    0       0x0208   16          CXPACKET                                   3
53     3408   0       0x0208   16          CXPACKET                                  4
53     2792   0       0x0208   16          CXPACKET                                  5
53     912    0       0x0208   16          CXPACKET                                   6
53     616    0       0x0208   16          CXPACKET                                   7
53     760    0       0x0208   16          CXPACKET                                   8 

I am aware that ecid = 0 corresponds to the parent thread.

QUESTION: Does this look OK?

Thanks for your help.

Thank you very much.

the cxpacket is a parallelism wait - you might want to experiment with the maxdop statement to see if using less procs speeds things up.

I see this with poor/complex sql where the cost generates a parallel plan but it actually slows things down.

I've just applied this to a 12 table cross database report - with all procs 18 - 20 secs --- with the maxdop hint  1 sec. ( 8 physical proc box running 16 with HT )

It's not that there is anything drastically wrong with the query - it's just messy < grin > .. sometimes it may be an indication of missing indexes.

Thank you very much for your input.

This is third party vendor performing a conversion. Looking that the script they are running and the indexing I agree with you that indexing is playing a role here.

I see this from time to time with ARCServe, on a SELECT and DELETE statements. One of the tables has about 91,000,000 records in it. Obviously being a third party application there's no way to re-engineer it and there are indexes where it matters. Tests defragging and reindexing show no effect it still occurs. One of our server engineers insists ARCServe wasn't designed to backup 150 servers.

We walk in the dark places no others will enter
We stand on the bridge and no one may pass