MAXDOP Settings for SQL Server 2014

Bogus

Here's why that wait stats report stinks: It doesn't tell you how long the server has been up.

I can see it in your screenshot of CPU time: 55 days!

Alright, so let's do some math.

Math

There are 86,400 seconds in day.

SELECT (86400 * 55) seconds_in_55_days

The answer there? 4,752,000

You have a total of 452,488 seconds of CXPACKET.

SELECT 4752000 / 452488 AS oh_yeah_that_axis

Which gives you... 10 (it's closer to 9.5 if you do actual math, here).

So while CXPACKET might be 62% of your server's waits, it's only happening about 10% of the time.

Leave It Alone

You've made the right adjustments to settings, it's time to do actual query and index tuning if you want to change the numbers in a meaningful way.

Other considerations

CXPACKET may arise from skewed parallelism:

• More on CXPACKET Waits: Skewed Parallelism

On newer versions, it may surface as CXCONSUMER:

• CXCONSUMER Is Harmless? Not So Fast, Tiger.

Absent a third party monitoring tool, it may be worth capturing wait stats on your own:

• Capturing wait statistics for a period of time
• How to Capture Baselines with sp_BlitzFirst

Wait stats are just numbers. If your server is doing anything at all then you'll likely to have some kind of waits appear. Also, by definition there must be one wait which will have the highest percent. That doesn't mean anything without some kind of normalization. Your server has been up for 55 days if I'm reading the output of task manager correctly. That means that you only have 452000/(55*86400) = 0.095 wait seconds of CXPACKET per second overall. In addition, since you're on SQL Server 2014 your CXPACKET waits include both benign parallel waits and actionable waits. See Making parallelism waits actionable for more details. I would not jump to a conclusion that MAXDOP is set incorrectly based on what you have presented here.

I would first measure throughput. Is there actually a problem here? We can't tell you how to do that because it depends on your workload. For an OLTP system you might measure transactions per second. For an ETL, you might measure rows loaded per second, and so on.

If you do have a problem and system performance needs to be improved I would then check CPU during times when you experience that problem. If CPU is too high then you probably need to tune your queries, increase server resources, or reduce the total number of active queries. If CPU is too low then you may again need to tune your queries, increase the total number of active queries, or there might be some wait type that's responsible.

If you do elect to look at wait stats, you should look at them only during the period in which you're experiencing a performance problem. Looking at global wait stats over the past 55 days simply is not actionable in almost all cases. It adds unnecessary noise to the data that makes your job harder.

Once you've completed a proper investigation it is possible that changing MAXDOP will help you. For a server of your size I would stick to MAXDOP 1, 2, 4, or 8. We cannot tell you which of those will be best for your workload. You need to monitor your throughput before and after changing MAXDOP to make a conclusion.