cancel
Showing results for 
Search instead for 
Did you mean: 

Wait type CXPACKET - BPC 5.1 on MSS 2005 SP3

Former Member
0 Kudos

We are experiencing large variations in run times of logic packages in a BPC 5.1 system. A package may run in 15 minutes at one time and then the next run may take 5 hours. When the run time is significantly longer we see a process in the MSS Activity Monitor that is running in parallel (multiple rows with the same SPID) and all but one are suspended. Each of the suspended threads shows CXPACKET in the "Wait type" column. The command column has the value INSERT.

The other observation is that the system appears to spawn more threads for the same SPID after a period of time. For example, I have often found there are 20 threads and the wait time will be the same for groups of four threads -- each group of four has a different wait time than the others in the list.

My research indicates that this means the process is running in parallel and the threads are waiting for the completion (or start) of parallel statements. The only suggestion I have found so far is to change "Max Degree of Parallelism" from 0 (zero) to 1 on the server to prevent parallel query execution, or to set a hint to set MAXDOP to 1.

Is the logic package creating inefficient query plans that cause MSS to generate inefficient query plans that go into parallel? Is there any way to prevent this from happening?

Thanks,

David

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

CXPACKET wait is due to parallelism, and as you have researched you can avoid it by setting MAXDOP to 1.

Yes, improper query plans may be cause for long time executions, you may want to check the statistics are updated,any missing indexes,lots of fragmentation.

you may want to look into this nice article, about how to tune your query against CXPACKET waits.

[http://www.mssqltips.com/tip.asp?tip=2027]

Thanks

Mush

Answers (0)