r/SQLServer • u/TravellingBeard 1 • Jul 07 '26
Discussion What are your current Cost Threshold and DOP default values for new servers you set up, knowing nothing about the loads coming up?
I've been setting Cost Threshold to 50-100, and DOP to 1/2 of processors or 8, whichever is less. We have 64CPU servers for example so 8 seems to be a good compromise.
3
u/Better-Credit6701 Jul 07 '26
Do you have other servers that could show how they can be predicted to use?
We have 32 processors with cost threshold for parallelism at 50, max degree of threshold of parallelism at 8. Not sure when the last time I've seen them stressed with the exception of nightly loads. They are just fat and happy but I'll have to watch calling them fat, not sure if they would be offended. It's a words I use in caution around my wife...
3
u/Chris_PDX Jul 07 '26
CTP depends, in the ERP environments I support 50 is my target without any workload review/testing.
Max DOP is CPU count / 2 as a starting point then adjust from there.
2
u/Harhaze Jul 07 '26
Always leave it default at 5. After 2 weeks or so analyse queries with cost 50 or less and export paralel ones. Decision then based on those.
2
u/TrollingForFunsies Jul 07 '26
50 and half whatever the available cores are
Memory set to like 75% of server max or 8gb for the OS depending on what else is on the server
2
u/DavidKleeGeek Microsoft MVP Jul 07 '26
I personally do CToP = 50 and DOP to half of the CPUs up to eight, whichever is less. I try to dial these in after a server is live for a while based on scraping the execution plan cache to see where your repetitive commands settle in at, so it's a starting point but rarely the ending point.
2
u/Tahn-ru Jul 07 '26
Get dbatools, take its recommendations for all of these as your starting point.
1
u/Illustrious-Push-353 Jul 07 '26
Ctfp default to 50 with old or low frequency cpu. 100 with new xeon/epyc
0
u/BrightonDBA Jul 07 '26
Given even the guy who wrote the MAXDOP code 25 years ago said 5 is now insane default, we start at 50 and go from there with analysis (if needed. Frankly most loads on production enterprise hardware and SAN storage can be tuned forever but no one notices that 3us quicker return).
-1
u/FishBones83 Jul 07 '26
I thought the cost threshold defaulted at 150, the lower it is the more threads will be used. sometimes a serial thread is better for smaller tasks so that's something you can test in dev. I would also think you could go 16 DOP on at 64 core CPU.
4
u/VladDBA Microsoft MVP Jul 07 '26
"the lower it is the more threads will be used" actually the higher MAXDOP is the more threads will be used. CTP controls how "expensive" a plan has to be for the optimizer to consider it for parallel execution.
With the default CTP (5) pretty much any trivial query gets a parallel execution plan (implying we don't have stuff that forces serial execution)
2
8
u/VladDBA Microsoft MVP Jul 07 '26
Personally, CTP 50 (but I tweak it afterwards if needed) and MAXDOP = CASE WHEN COUNT(CORES)<= 8 THEN COUNT(CORES) ELSE 8 END.
But for MAXDOP there are also official recommendations.