SQL Server - Making MAXDOP Change: How to measure the effect on performance?

Question

I am attempting to reduce CXPACKET waits in my SQL Server 2012 databases.

I am going to adjust the MAXDOP and Cost Threshold for Parallelism in order to do so. See Brent Ozar's article.

In order to gauge the effect that the changes have on wait times I am tracking the wait time every 15 mins using sys.dm_os_wait_stats and following this advice. I want to take 2 weeks of readings before I adjust and 2 weeks after.

But, I am also interested in tracking overall query performance. What would be a good way to see how the queries are performing before and after the changes - over the same 2 week before / 2 week after timeframe? Are there sprocs that will give me this data?

Answer 1

Based on the above, it seems you could some utility to help you monitor query performance.

I can suggest ApexSQL Monitor (a commercial tool, but offers a free trial) that can store historic information, so you can review details before and after reducing MAXDOP - and you can review the overall query performance.

Besides that, you can also find a good explanation on possible causes about CXPACKET and MAXDOP in this article http://www.sqlshack.com/troubleshooting-the-cxpacket-wait-type-in-sql-server/. Here are some key takeaways from the article:

The steps that are recommended in diagnosing the cause of high CXPACKET wait stats values (before making any knee-jerk reaction and changing something on SQL Server):

• Do not set MAXDOP to 1, as this is never the solution
• Investigate the query and CXPACKET history to understand and determine whether it is something that occurred just once or twice, as it could be just the exception in the system that is normally working correctly
• Check the indexes and statistics on tables used by the query and make sure they are up to date
• Check the Cost Threshold for Parallelism (CTFP) and make sure that the value used is appropriate for your system
• Check whether the CXPACKET is accompanied with a LATCH_XX (possibly with PAGEIOLATCH_XX or SOS_SCHEDULER_YIELD as well). If this is the case than the MAXDOP value should be lowered to fit your hardware
• Check whether the CXPACKET is accompanied with a LCK_M_XX (usually accompanied with IO_COMPLETION and ASYNC_IO_COMPLETION). If this is the case, then parallelism is not the bottleneck. Troubleshoot those wait stats to find the root cause of the problem and solution

Answer 2

In an environment where a lot of large queries are ran you'll always see CXPacket towards the top of the list of waits, and it's not an issue. The only issue is when that starts skyrocketing over what your normal waits are. In those cases it typically means that your stats are so far off that it's not getting an accurate execution plan and you need to look into better index maintenance.

From what you have said, there's no reason I'd lower MaxDOP based on that alone. Instead I'd go with the typical advice of up to 8 with no more than the number of cores in a single NUMA node for OLTP.

In your case, I'd start looking at your most expensive queries using Query Stats along with your largest queries according to a server-side trace. If you're able to tune the biggest offenders you see here then you'll have a faster server along with a lowered CXPacket wait type.

As a bit of a disclaimer, I am the author of the wait stats article you mentioned as well as the articles linked to in this response.

Please feel free to reply here for this question or on my blog for any others that come up.

Answer 3

CXPACKET waits don't necessarily indicate a problem with parallelism - it's usually a symptom of some other problem.

When a query goes parallel, let's say across 10 threads, and one of those 10 threads takes longer than the others to finish its work, the other 9 threads are going to accumulate CXPACKET waits.

What other high wait types are you seeing?