Maximizing SQL Server Performance with Parallelism: Understanding Query, Database, and Server Settings (MAXDOP)

 

Parallelism is a key feature of SQL Server that enables multiple processors to work on a single query simultaneously. By utilizing parallelism, you can significantly improve query performance and reduce query execution time. However, to achieve optimal performance with parallelism, it is important to understand the priority of settings on query, database, and server levels. In this blog post, we will discuss the priority of settings for parallelism at the query, database, and server level.

Level of parallelism settings: 

Query Level: The MAXDOP query hint allows you to control the degree of parallelism for a specific query. This will override all MAXDOP setting for Database and server level for the specific query.

 For example, the following query uses a MAXDOP hint to limit the query to four processors:

SELECT * FROM [Person].[Address] OPTION(MAXDOP 4)

Database Level: The MAXDOP database setting allows you to specify the maximum degree of parallelism for all queries on a specific database. For example, the following command sets the MAXDOP setting to 4 for the SampleDatabase database:

 

USE [master]
GO

GO
USE [SampleDatabase]
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;
GO

To check the currently configured value of MAXDOP, use the sys.database_scoped_configurations system catalog view.

SELECT
    *
FROM
    sys.database_scoped_configurations
WHERE
    [name] = 'MAXDOP';

Cost Threshold for Parallelism: This setting determines the minimum query cost for which parallelism is used. Queries with a cost below the threshold will not use parallelism. Setting a higher cost threshold can reduce the number of queries that use parallelism, which can improve resource utilization and reduce contention.

Processor Affinity: This setting specifies which processors or cores can be used for SQL Server instance. It can limit the number of processors available for query execution and affect parallelism. 

Server Level: The MAXDOP server setting allows you to specify the maximum degree of parallelism for all queries on the SQL Server instance. For example, the following command sets the MAXDOP setting to 4 for the entire server:

 

USE [master]
EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max degree of parallelism', N'4'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

Use below script to find maxdop for server level

SELECT
    c.name,
    c.value,
    c.value_in_use,
    c.description,
    socket_count = CASE
        c.name
        WHEN N'max degree of parallelism' THEN osi.socket_count
        ELSE NULL
    END,
    cores_per_socket = CASE
        c.name
        WHEN N'max degree of parallelism' THEN osi.cores_per_socket
        ELSE NULL
    END
FROM
    sys.configurations AS c
    CROSS JOIN (
        SELECT
            osi.socket_count,
            osi.cores_per_socket
        FROM
            sys.dm_os_sys_info AS osi
    ) AS osi
WHERE
    c.name IN (
        N'cost threshold for parallelism',
        N'max degree of parallelism'
    );

Maximizing Performance with Parallelism: 

1. Use the appropriate level of parallelism for each query based on the workload and available hardware resources.
2. Monitor query performance and adjust MAXDOP and Cost Threshold for Parallelism as needed to balance performance and resource utilization.
3. Avoid excessive parallelism, which can result in decreased performance due to increased overhead and contention.
4. Consider using processor affinity to limit resource contention and improve query performance.

Parallelism is a powerful feature in SQL Server that can significantly improve query performance. However, it requires careful management to avoid resource contention and performance degradation. By understanding the priority of parallelism settings at the query, database, and server level, and managing them effectively, you can optimize query performance and improve overall database performance.