How to Use MAXDOP Effectively in SQL Server

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.

Prerequisites

To follow along, ensure you have:

• SQL Server installed in your development environment
• A basic understanding of parallelism

Best Practices for Tuning MAXDOP

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.

Set the MAXDOP Value

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.

How to Set the MAXDOP Value at the Server Level

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:

USE <database_name>; 
GO 
EXEC sp_configure 'show advanced options', '1'; 
GO 
RECONFIGURE WITH OVERRIDE; 
GO 
EXEC sp_configure 'max degree of parallelism', '16'; 
GO 
RECONFIGURE WITH OVERRIDE; 
GO  

This sets the MAXDOP value to 16.

How to Set the MAXDOP Value at the Workload Group Level

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> 
WITH 
    (MAX_DOP = 4) 
USING <pool_name>; 
GO  

How to Set the MAXDOP Value at the Database Level

You can set the MAXDOP value at the database level using the code below:

USE <database_name>; 
GO 
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 16; 
GO  

How to Set the MAXDOP Value at the Query Level

You can also set the MAXDOP value at the query level using query hints, as shown in the illustration code below:

SELECT * 
FROM <table> 
OPTION (MAXDOP 2); 
GO  

MAXDOP Configuration Considerations

When deciding where to set the MAXDOP value, consider the following:

• A database-scoped configuration overrides the server configuration, up to the limit set at the workload group level.
• A query-scoped configuration overrides the MAXDOP value specified at the database and server levels, up to the limit set at the workload group level.
• A workload group configuration limits non-zero MAXDOP values set at all other levels, and overrides MAXDOP values set to 0.

You can mix and match these three configurations to achieve the best performance for each query.

Get the NUMA Configuration

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:

SELECT @@SERVERNAME, 
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]  
FROM  
sys.dm_os_sys_info;  

In the code snippet above:

• ComputerNamePhysicalNetBIOS is the NetBIOS name of the machine where the SQL Server instance is running.
• cpu_count is the number of logical CPUs.
• hyperthread_ratio is the number of CPUs exposed on a single socket.
• softnuma_configuration is set to one of the following:
  • 0 (configuration is off and the hardware defaults are used)
  • 1 (automated soft-NUMA)
  • 2 (manual soft-NUMA configuration through the registry)
• softnuma_configuration_desc is either:
  • OFF (the soft-NUMA feature is off)
  • ON (the SQL Server automatically determines the NUMA node sizes)
  • MANUAL
• socket_count is the number of available processor sockets.
• numa_node_count is the number of available NUMA nodes.

Once you get the NUMA node configuration, you can fine-tune the MAXDOP settings based on it.

Fine-Tune MAXDOP Settings

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:

• The number of NUMA nodes (single or multiple)
• The number of processors per NUMA node

Below are some recommendations for MAXDOP values based on the number of NUMA nodes and logical processors.

Set the Value with NUMA Nodes in Mind

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:

• Set the MAXDOP value equal to or less than the number of logical processors if the server has less than or equal to eight logical processors.
• Set the MAXDOP value to 8 if the number of logical processors is greater than eight.

For a server with multiple NUMA nodes:

• Set the MAXDOP value equal to or less than the number of logical processors per NUMA node if the server has less than or equal to sixteen logical processors per NUMA node.
• Set the MAXDOP value to half the number of logical processors if the processors exceed sixteen per NUMA node. At maximum, the MAXDOP value should be 16.

Establish a Baseline

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.

Consider the Parallelism Threshold

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.

Conclusion

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.