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?