By 
Photo by Sarah Klockars-Clauser
Scenario
Your SQL box has consistently high CPU usage, possibly pegged at 100%. You look at your favorite DMV or (god forbid) Activity Monitor and see one or more active queries with a CXPACKET wait type.
What do you do???
(Try not to Google it.)
Solution
I specifically remember my MCITP training and tests drilling CXPACKET waits and max degrees of parallelism (MAXDOP) into my head to the point where I can’t think of one without the other. And for a good reason! Changing max degrees parallelism can reduce CXPACKET waits, BUT it can also severely damage query performance if you don’t know what you’re doing.
So before you play with max degrees of parallelism, consider a much safer alternative: run the corresponding query through the Database Engine Tuning Advisor. More than likely, DTA will suggest some indexes and possibly some statistics that could drastically improve your query performance. I was able to achieve a 100% improvement on one problem query, which in turn reduced CPU usage to almost nothing. Your results may vary.
But perhaps you’re not as lucky as I was. Perhaps DTA couldn’t suggest any indexes or statistics, or the improvement was negligible. What do you do then?
Assuming you can’t change the underlying architecture or process and your system isn’t underpowered or overutilized (how good are you at begging?), you can reduce the max degrees of parallelism with a query hint or by changing your instance config, or you can increase the cost threshold for parallelism at the instance level. However, I don’t recommend changing either of these before doing a LOT more research to understand all the queries that run on your instance. This is serious business.
For a more detailed look at CXPACKET waits, check out Brent Ozar’s blog. He and his team are a wealth of knowledge on all things SQL Server.
For more info on all types of waits in SQL Server, check out the MSDN entry for sys.dm_os_wait_stats
By