0

When upgrading and creating my new SQL server farm on 2016 I researched things to set on the server, one being MAXDOP setting.

From reading up about the setting I have set it at 8 on a server that has 8 logical CPUs. The server is a VM and I believe it has a single NUMA node. The worload is OTLP.

However I recently went on a training course where the suggestion was to set MAXDOP to 4 so I have started to second guess myself about this setting.

CTFP is set at 50.

I would appreciate help in determining the correct setting for this.

Garry
  • 53
  • 1
  • 6

1 Answers1

4

The actual value to use for your MAXDOP is going to vary by your workload. If it's truly pure OLTP then you should probably get away with setting it to 1 and be done.

HOWEVER

We (the royal we) almost never get away with a pure OLTP workload, there are always going to be reports driven off of it, or screens in the app that look at lots of data at once, or you have to SHARE the server with BI. So a more reasonable number is usually selected.

We have a 24 core server (for the memory, SQL Managed Instance) and have set our MAXDOP to 8 and our CTFP to 50. I am currently toying with the idea of increasing CTFP as many of the times when we have problems it is because a largish query has gone parallel and has tied up resources doing so. No benefit to speed, but large increase in resources required. In fact, many of our larger queries have MAXDOP hints limiting them to single thread.

For an 8 core server, I would set MAXDOP to 4 and leave CTFP where it is now (50). For larger number of cores, I would set MAXDOP to 8 and start with CTFP at 50. Then look at your workload, especially for queries with high CX waits. If the majority of your workload is still going parallel and shouldn't, tweak the numbers. Otherwise, tune the queries that are going parallel that shouldn't.

Jonathan Fite
  • 9,414
  • 1
  • 25
  • 30