Baseline your servers and optimize your applications with Site24x7 SQL monitoring tool.
One of the SQL Server instance properties you can tweak to optimize queries is the Maximum Degree of Parallelism (MAXDOP). MAXDOP is a server configuration that enables you to run the SQL Server across multiple CPUs. It also determines the number of processors used for parallel plan execution.
For queries running in parallel, the execution process distributes worker threads to several logical processors based on the MAXDOP value. Configuring the MAXDOP to a correct value can help you reduce query execution times and maximize query performance.
For SQL Server 2016 and later, the MAXDOP default value depends on the calculations based on the number of processors available during installation. Notably, the default value might work for most queries but not all. Furthermore, your hardware configuration may change, and the MAXDOP default value might reflect something else. Therefore, we recommend changing this value to match your specific environment and workload type.
The MAXDOP value affects the performance of your queries, and a wrong value could result in performance losses. Large single queries will take longer to execute if the value is too small. But, if the value is too large, multiple queries can overload the server.
This hands-on demonstration will review how to fine-tune your MAXDOP value and discuss the best practices to do so successfully.
To follow along, ensure you have:
Determining the ideal setting for the MAXDOP value depends on your hardware, environment, load, individual SQL statement, and other factors. Therefore, setting the optimal MAXDOP value requires you to experiment and fine-tune it to get it right.
The MAXDOP value should be based on the non-uniform memory access (NUMA) configuration. You need to determine how many logical processors you have in a NUMA node, then set your value according to Microsoft’s recommendations. If the recommended values don’t function for your workload, you’ll need to fine-tune the value until you get better performance.
You can override the MAXDOP value you set at the server level with MAXDOP values at the database, query, or workload group levels.
The method you should choose depends on how well your database and application perform. Just keep in mind that there are precedence levels. Workload group configurations override the rest of the configurations.
Query-level configurations override database-scoped and server-level configurations.
For example, the server-level configuration affects all the instances running on the SQL Server. While this level may work for most of the databases on the SQL Server, it might not work for all. So, instead of reconfiguring the server, set the MAXDOP value for those databases with lagging performance. But remember, this value may only work for some queries in that database. This is when the query hints come in handy, as you can use them to optimize the performance at the query level.
The following section will highlight how to set the value at the various levels.
In addition to using SQL Server Management Studio to configure MAXDOP, you can also set the MAXDOP value at the SQL Server instance level using the following Transact-SQL (T-SQL) code:
EXEC sp_configure 'show advanced options', '1';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'max degree of parallelism', '16';
RECONFIGURE WITH OVERRIDE;
This sets the MAXDOP value to 16.
You can set the MAXDOP at the level of the workload group by associating it with a Resource Governor resource pool using the following code:
CREATE WORKLOAD GROUP <group_name>
(MAX_DOP = 4)
You can set the MAXDOP value at the database level using the code below:
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 16;
You can also set the MAXDOP value at the query level using query hints, as shown in the illustration code below:
OPTION (MAXDOP 2);
When deciding where to set the MAXDOP value, consider the following:
You can mix and match these three configurations to achieve the best performance for each query.
NUMA’s purpose is to configure a cluster of microprocessors so they can share memory locally. It helps increase memory access speeds and improve the system’s performance.
The illustration query below illustrates how you can query the current NUMA configuration for SQL Server from 2016 onwards:
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [Local Machine Name],
(cpu_count / hyperthread_ratio) AS [Physical CPUs],
hyperthread_ratio AS [Hyperthread Ratio],
cpu_count AS [Logical CPUs],
softnuma_configuration AS [Soft-NUMA Configuration],
softnuma_configuration_desc AS [Soft-NUMA Description],
socket_count AS [Available Sockets],
numa_node_count AS [Available NUMA Nodes]
In the code snippet above:
Once you get the NUMA node configuration, you can fine-tune the MAXDOP settings based on it.
As previously mentioned, a MAXDOP value that works well in one system for a specific query may provide different results performance-wise in another system. Therefore, we recommend tweaking the value and monitoring any bottlenecks indicating slow-downs.
The primary factors that determine which MAXDOP value to use are:
Below are some recommendations for MAXDOP values based on the number of NUMA nodes and logical processors.
NUMA is a design approach that places the memory bank adjacent to the CPU socket. Each combination of the memory and CPU socket is called a node. Microsoft recommends that the MAXDOP be set based on the number of NUMA nodes and processors. The aim is to ensure the query doesn’t run outside the bounds of a given node. Performance may reduce as the query will be accessing foreign memory, which is costly. Ideally, any query that runs in parallel should use the same processor and access your local memory.
Below are Microsoft’s recommendations for MAXDOP values for SQL Server 2016 and on.
For a server with a single NUMA node:
For a server with multiple NUMA nodes:
To measure a MAXDOP value’s effectiveness, you must establish a baseline and compare the performance change against it. This means running tests with different values, collecting wait stats, and recording the costs of your most resource-intensive queries in the plan cache over a certain period, then making the MAXDOP change and collecting the data again. You can change the value by 1 or 2 and monitor it over a week.
Review the information before and after changing the MAXDOP to determine how the new MAXDOP value affects query performance.
When fine-tuning the MAXDOP, you must consider the parallelism’s cost threshold, which determines which queries you can execute in parallel. If a server’s cost threshold of parallelism is 5, any queries with a cost of 5 and above will be executed across multiple processors, up to the MAXDOP value. Cost represents an abstracted measure of processing on a specific hardware configuration rather than a measure of time.
Together, the cost threshold of parallelism and the MAXDOP value determine how costly a query must be to be executed in parallel and how many parallel threads can be assigned. When setting these values, the idea is to assign extra threads to large queries, which are more efficiently executed in parallel, but to avoid running out of worker threads because of a high degree of parallelism.
If there are top queries with high logical reads that are executing slowly, they could benefit from parallelism, so consider reducing the cost threshold. After they go parallel, increase the MAXDOP value gradually and see whether the server’s wait durations improve.
In SQL Server 16 and later, the DOP feedback feature uses the query store to intelligently self-adjust the MAXDOP. Instead of manually adjusting each query, DOP feedback reduces excess parallelism on a per-query basis and produces more resource-efficient and scalable workloads.
While some SQL Server instances might perform fine with the default MAXDOP setting, you sometimes need to change the MAXDOP value to optimize query performance. The MAXDOP value determines the number of processors a query running in a parallel plan execution can use. But a higher MAXDOP doesn’t mean faster execution times. Instead, you must fine-tune the MAXDOP settings until you achieve your desired performance level.
When changing the MAXDOP value, get the NUMA node configuration first. Depending on your workload, you can set this value at the server, database, or query level. The value at the server level applies to the entire SQL Server instance unless a database has its own MAXDOP value. Similarly, the database MAXDOP value applies to the whole database unless you set it at the query level.
To properly fine-tune a MAXDOP value, establish a baseline first. This means having a proper benchmark you can run with different settings. Be sure to repeatedly monitor the performance from these runs while changing the settings until you get the MAXDOP value that works best for your workload.
Explore high availability methods for Microsoft SQL Servers such as data mirroring & Always On Availability for robust data protection. Learn more!➤
Learn how to optimize and improve SQL queries on SQL servers handling large datasets following the best practices. Learn More➤
Write for Site24x7 is a special writing program that supports writers who create content for Site24x7 “Learn” portal. Get paid for your writing.Apply Now