What value need to be set for MAXDOP ?
-
Hi All,
Need some advice on setting the parallelism settings.
What value to be set for MAXDOP and cost threshold for parallelism? This is a OLTP instance.
Right now I see the max degree of parallelism = 1 and cost threshold for parallelism = 5.
Environment details
===============
OS Name Microsoft Windows Server 2012 R2 Datacenter
Version 6.3.9600 Build 9600
Other OS Description Not Available
OS Manufacturer Microsoft Corporation
System Manufacturer VMware, Inc.
System Model VMware Virtual Platform
System Type x64-based PC
System SKU
Processor Intel(R) Xeon(R) CPU E5-2697 v3 @ 2.60GHz, 2594 Mhz, 2 Core(s), 2 Logical Processor(s)
Processor Intel(R) Xeon(R) CPU E5-2697 v3 @ 2.60GHz, 2594 Mhz, 2 Core(s), 2 Logical Processor(s)
Processor Intel(R) Xeon(R) CPU E5-2697 v3 @ 2.60GHz, 2594 Mhz, 2 Core(s), 2 Logical Processor(s)
Processor Intel(R) Xeon(R) CPU E5-2697 v3 @ 2.60GHz, 2594 Mhz, 2 Core(s), 2 Logical Processor(s)
Installed Physical Memory (RAM) 120 GB
Total Physical Memory 120 GB
Available Physical Memory 2.77 GB
Total Virtual Memory 134 GB
Available Virtual Memory 14.2 GB
Page File Space 14.0 GB
Page File C:\pagefile.sys
A hypervisor has been detected. Features required for Hyper-V will not be displayed.
SQL Server version : Microsoft SQL Server 2016 EE
-- Hardware information from SQL Server 2012 (Query 17) (Hardware Info)
SELECT
cpu_count/hyperthread_ratio AS [Physical CPU Count],
cpu_count AS [Logical CPU Count],
scheduler_count,
hyperthread_ratio AS [Hyperthread Ratio],
physical_memory_kb/1024 AS [Physical Memory (MB)],
committed_kb/1024 AS [Committed Memory (MB)],
committed_target_kb/1024 AS [Committed Target Memory (MB)],
max_workers_count AS [Max Workers Count],
affinity_type_desc AS [Affinity Type],
sqlserver_start_time AS [SQL Server Start Time],
DATEDIFF(hour, sqlserver_start_time, GETDATE()) AS [SQL Server Up Time (hrs)],
virtual_machine_type_desc AS [Virtual Machine Type]
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);
Go
-- SQL Server NUMA Node information (Query 13) (SQL Server NUMA Info)
SELECT node_id, node_state_desc, memory_node_id, processor_group, online_scheduler_count,
idle_scheduler_count, active_worker_count, avg_load_balance, resource_monitor_state
FROM sys.dm_os_nodes WITH (NOLOCK)
--WHERE node_state_desc <> N'ONLINE DAC' OPTION (RECOMPILE);
go
Thanks,
Sam
-
There is no "fits all" solution but this Article by Kendra should give you some guidance in regards to MaxDOP and CTFP.
-
Determining the cost threshold can be hard. However, I have a blog post that uses your own queries as a mechanism for making that choice. Suffice to say, the default value of 5 is just wrong. However, you can use your queries to make an initial setting. That may need to be adjusted up or down, but it should suffice to get you very close to what you need quickly and easily.
The setting for max degree of parallelism is highly dependent on your hardware and workload. Generally speaking 0, the default, is probably wrong. The other value people use all the time is 1, to eliminate all parallelism. Makes you wonder why they have multiple processors in the first place. I would argue that, most of the time, this is also wrong, after you change the cost threshold. For a small system like you're showing, with four processors, maybe 2. Again, after setting the cost threshold.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I agree with the others about MAXDOP and Cost Threshold for Parallelism (CTP from here on) being more of an observational art rather than a science. I also agree that the default of "5" is almost always wrong (haven't seen where it's been right, yet, but leaving room for an extremely rare exception).
That being said, I do have a personal, very general starting point guideline that I use on new or unknown systems that suffer a combination of OLTP and batch jobs that are hit by both 24/7...
I generally set MAXDOP to the number of core divided by 4 (rounded down to the nearest core) as a starting point. For systems with less than 8 core, it's going to require an analysis of workload by time-of-day and you may actually have to schedule a job to change it for nightly batch jobs. My upper limit on this is MAXDOP 8. If you need more than that, you have a real problem in your code that needs to be fixed immediately.
For the CTP, I fire up PerfMon and watch CPU, Disk Reads, and Disk Writes. Once I have a feel for what's happening, I'll change the CTP from 5 to 25 and see what happens for a day or two. Of course, that's going to require you to monitor PerfMon more than once per day (you might be able to use some of the new OS DMVs to automatically do the monitoring for you). Generally and by my observation (which means YMMV), 25 usually turns out pretty good for the type of systems I've had to work on. Others say 50 or so has been their magic number on their systems. Again, YMMV. But and again, to Grant's excellent point, 5 is the wrong setting and it needs to be increased.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you Sir. will go through it.
-
Thanks all for the inputs. Will do some testing in my environment before I come to any conclusion.
-
Thanks for the feedback, Sam. I'd love to hear what you end up with.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply