GilaMonster (1/4/2010)
Please post new questions in a new thread in future.If you look while the query is running, there will be at least one thread that doesn't have a cxpacket, that has a different wait type. Whatever that is is what's holding the query up. What is it?
I usually recommend generic performance tuning (tune the query, tune the indexes) before panicking over CXPackets. It's quit common to see them on non-optimised queries.
A perfect example of this is this statement: " but in some cases I could greatly reduce query execution time (and CXPACKET waits) by using the OPTION LOOP JOIN"
that is a clear indication of a suboptimal query.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service