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.

0
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.

7
  • Why? Why 50? This feels fairly arbitrary to me. Nov 10 '20 at 14:11
  • 50 is my usual starting number and typically does the job well enough for me. The goal is to keep simple queries single threaded but still allow sufficiently complex queries to go parallel if they need to. The default weights from the engine are sub-optimal for current gen CPU's at least as far as this measure goes. For the most part, this works well enough, the queries I've found going parallel in my environment now are usually so gnarly that a higher number wouldn't have stopped them from going parallel. Nov 10 '20 at 14:16
  • Completely appreciate that, but why 50? Random start point? Nov 10 '20 at 14:18
  • oh, as for why that's my start point? bit of wisdom I picked up somewhere along the way. It's a good enough number for starting out and better than the default value of 5. I do periodic evaluations of the workload and wait stats. So far tuning the queries has been good enough and that's definitely where I would start. But if I inherited a new server and every/most queries were doing bad things, I would definitely start tweaking those numbers to see if that would help. Nov 10 '20 at 14:23
  • 2
    @George.Palacios It's arbitrary. Collectively we know 5 is way too low for OLTP, but collectively we don't know whether 50 is too high or not high enough until we try it. It's a pretty reasonable place to start, but the answer to your question would be the same if he said 10 or 70 or 2000... it's an arbitrary number by design, it's just a better arbitrary number than 5. IMHO, anyway. If you don't like 50, what's your fairly arbitrary suggestion? Nov 10 '20 at 16:04

Not the answer you're looking for? Browse other questions tagged or ask your own question.