What MAXDOP setting should be used for SQL Server
Solution
The purpose of this article is to share with the community a discussion I had with a Microsoft PFE (Premier Field Engineer) about the max degree of parallelism (MAXDOP) setting. This article, is not a deep and comprehensive discussion of what MAXDOP is, but rather knowledge shared from the field. I have a great respect for Microsoft PFE's, because they are extremely knowledgeable and their experience comes directly from day-to-day interactions with clients. They deal with complex issues on a daily basis, so I thought the information they shared with me would be beneficial for other DBAs.
What is MAXDOP and why it is important?
When SQL Server runs on a computer with more than one processor or CPU, it detects the best degree of parallelism, that is the number of processors employed to run a single statement, for each query that has a parallel execution plan. You can use the max degree of parallelism option to limit the number of processors to use for parallel plan execution and to prevent run-away queries from impacting SQL Server performance by using all available CPUs.
What values should I use for MAXDOP?
To answer the question: What values should I user for MAXDOP? The answer is: It depends. It depends on the hardware, the environment (OLTP vs. OLAP), the load and so on.
The default value for MAXDOP is 0 (zero) and can be set or viewed using (sp_configure). A value of 0 means that SQL Server will use all processors if a query runs in parallel.
Below we can see the current value if we run sp_configure.
How do I change MAXDOP using SSMS or T-SQL?
Below shows the T-SQL command that you can run to change the value. In this example I am changing the value to 4. This means if a query uses a parallel execution plan it will only use four of the available processors.
Within SSMS, right click on a registered server and select Property. Then go to the Advanced page as shown below. You can then change this value and click OK to save the value.
After making this change the value goes into affect immediately, there is not a need to restart SQL Server.
What value should be used for MAXDOP?
The Microsoft Premier Filed Engineer gave us some guidelines to follow and consider when setting the MAXDOP feature.
| Environment | Settings |
|---|---|
| Hyper-Threading is Enabled | MAXDOP should not be 0 and should not be greater than half the number of visible schedulers. For example if you have a quad core processor with hyper-threading enable, then you will have 4x2=8 visible schedulers (each scheduler is mapped to an individual processor). Schedulers can be seen by running this query and would be the rows that have a scheduler_id < 255: SELECT * FROM sys.dm_os_schedulers WHERE scheduler_id < 255; |
| If Processor Affinity is set in SQL Server or WSRM (Windows System Resource Manager) is used to limit the number of cores available for SQL Server. Note: processor affinity consists of assigning specific threads to specific processors in order to prevent the OS to move threads amongst available processors. WSRM also can be used to allocate processor and memory resources to applications, users, Remote Desktop Services sessions, and so on... |
MAXDOP should be no more than the number of cores available to the SQL Server instance. So if you only allow SQL to use 4 processors via the processor affinity option this value should be 4 or less. |
| Environment uses hard NUMA | MAXDOP
should be no more than the number of cores per NUMA node to avoid
expensive foreign memory access that occurs, when a task needs to use
memory that does not belong to its NUMA node. |
| Generic Servers or general settings | If
you are unsure of the above values then a generic setting for MAXDOP
should not be more than 8. So if you run the command below and have
more than 8 you should set this value to a maximum of 8. SELECT * FROM sys.dm_os_schedulers WHERE scheduler_id < 255; |
What is hyper threading and how can I tell if it is enabled?
Hyper-Threading technology is a performance feature that allows one core on the processor to appear like 2 cores to the operating system. This doubles the execution resources available to the Operating System. Most of today's hardware allows you to enable or disable Hyper-Threading at the BIOS level. A quick way to see if Hyper-Threading is enabled is to view your CPU information using Task Manager in Windows and if it is enabled you should see twice the number of CPUs as you have processor cores in your server.
What is NUMA ?
Numa stands for Non-Uniform Memory Access it is a computer memory design used in multiprocessing, where the memory access time depends on the memory location relative to a processor. Under NUMA, a processor can access its own local memory faster than non-local memory, that is, memory local to another processor or memory shared between processors. To learn more about NUMA please check Understanding Non-uniform Memory Access.
Next Steps
To learn more about using the above-referenced components, check out some of these previous tips and resources below:
