November 9, 2020 at 3:15 pm
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 cores. 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.
November 9, 2020 at 4:24 pm
If hyperthreading is present (most CPUs use this nowadays), set MAXDOP to no more than half the cores, in your case 4.
If NUMA is being used, set it to no more than the number of cores in one NUMA node.
If you're on SQL 2016, you can get more info about these details by looking at select * from sys.dm_os_sys_info.
Certain times during off hours if I have a really intensive process I want to speed up, I will exceed the first guideline (but not the NUMA one). For example, I might specify MAXDOP = 6 on a really big rebuild.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 11, 2020 at 4:22 pm
This article states you should keep MAXDOP at or below 8 with 8 processors with 1 NUMA node. In my experience with a server with near identical resources, I've set it at 4 because we had process that would hog all cores and cause CPU related wait types for everything else. Because we didn't necessarily care how long this one process took, but definitely cared about the other processes waiting, we decided to set MAXDOP to 4.
I think in our case, we did what was best: We did start with 8, adjusted down to 6 due to the waits, and finally settled at 4 after finding waits were still too bad. Might be worth following a similar plan.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply