SQL Server Parallelism: Configuring MAXDOP and Cost Threshold

SQL Server can use multiple CPU cores to execute a single query, a technique called parallelism. This can improve performance for heavy queries, but when misconfigured, it can also lead to significant slowdowns and CPU resource contention.

Several operators in an execution plan can be parallelized, allowing SQL Server to divide the work among multiple execution threads. For example, a table scan or a join can be distributed across multiple cores, which work simultaneously to process data faster. This sounds like a good idea, but multithreading has a cost in terms of thread management and synchronization. Finding the right balance is key.

By default, SQL Server is configured to trigger parallelism too quickly, mainly because the threshold and core count were defined in the late 1990s, when servers had much less power and fewer cores. These values are no longer suitable for modern environments.

There are two values that should always be changed in the instance configuration.

Cost Threshold for Parallelism (CTFP)

The Cost Threshold for Parallelism defines the estimated cost of a query at which SQL Server decides that a query is “heavy” enough to be divided among multiple processors.

By default, this value is set to 5. This number dates back to the late 1990s. Today, with the power of modern processors, a query with a cost of 5 executes in a few milliseconds.

Asking SQL Server to coordinate multiple cores for such a trivial task costs more resources (in thread management) than executing the query itself. This is what often causes the CXPACKET (or CXSYNC_PORT) wait type.

Recommended Values

By raising this value to 50, you force small queries (OLTP) to remain on a single core. This frees up other processors to handle more simultaneous connections. Parallelism will only be enabled for reporting or maintenance queries that are truly resource-intensive.

Over the years, we have increased this value. I now believe you can push it up to 70.

Do your own testing—this is a value configured at the instance level, and you can adjust it on the fly. As soon as the option is changed, SQL Server applies it immediately for all new queries.

Max Degree of Parallelism (MAXDOP)

The Max Degree of Parallelism (MAXDOP) limits the number of CPU cores that a single query can use, or more precisely, that a parallelized section of a query can use.

By default, MAXDOP is set to 0, which means: “Use all available processors”. Theoretically, you will read in the documentation that SQL Server is supposed to choose an optimal number of cores based on server load. In practice, it will always use the maximum number of defined cores. This is one of SQL Server’s mysteries.

If you have a server with 32 cores and a user runs a relatively light query (just above 5), SQL Server may use all 32 cores to scan a modest table, and then waste time synchronizing threads.

MAXDOP Best Practices

• Never exceed 8: Even on massive servers, it’s rare for a query to gain efficiency beyond 8 cores (synchronization overhead becomes too heavy).
• For OLTP (transactional): A value of 4 is often the ideal compromise. This allows fast execution without monopolizing the processor.
• NUMA architecture: The golden rule is to never exceed the number of physical cores in a single NUMA node.

Applying Settings to the Instance

These changes are applied on the fly, without restarting the instance.

-- Enable advanced options
EXEC sys.sp_configure N'show advanced options', N'1';
RECONFIGURE;

-- Set cost threshold to 50
EXEC sys.sp_configure N'cost threshold for parallelism', N'50';

-- Limit global MAXDOP to 4
EXEC sys.sp_configure N'max degree of parallelism', N'4';

RECONFIGURE;

-------------------------------------------------------------------------------
-- essential information to get from a SQL Server when you open it 
-- for the first time
-- rudi@babaluga.com, go ahead license
-------------------------------------------------------------------------------

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;


SELECT 'windows_release' as info, windows_release as value
FROM sys.dm_os_windows_info
UNION ALL
SELECT 'total_physical_memory_gb', total_physical_memory_kb / 1024 / 1024.0
FROM sys.dm_os_sys_memory
UNION ALL
SELECT 
	'NUMA_nodes', COUNT(DISTINCT parent_node_id)
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE' AND is_online = 1
UNION ALL
SELECT 
	'CPUs', COUNT(*)
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE' AND is_online = 1
UNION ALL
SELECT name, value
FROM sys.configurations
WHERE configuration_id = 1581
UNION ALL
SELECT name, value
FROM sys.configurations
WHERE configuration_id = 1544
UNION ALL
SELECT name, value
FROM sys.configurations
WHERE configuration_id = 1539
UNION ALL
SELECT name, value
FROM sys.configurations
WHERE configuration_id = 1538
UNION ALL
SELECT RTRIM(counter_name), cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
AND object_name LIKE '%Buffer Manager%'
UNION ALL
SELECT N'Buffer cache hit ratio', CAST((ratio.cntr_value * 1.0 / base.cntr_value) * 100.0 AS NUMERIC(5, 2))
FROM sys.dm_os_performance_counters ratio
JOIN sys.dm_os_performance_counters base
	ON ratio.object_name = base.object_name
WHERE RTRIM(ratio.object_name) LIKE N'%:Buffer Manager'
AND ratio.counter_name = N'Buffer cache hit ratio'
AND base.counter_name = N'Buffer cache hit ratio base'
UNION ALL
SELECT  'instant_file_initialization', instant_file_initialization_enabled
FROM    sys.dm_server_services
WHERE   servicename LIKE 'SQL Server (%'
OPTION (RECOMPILE, MAXDOP 1);

Managing MAXDOP More Precisely

Before 2016, if you had a production database (OLTP) and a reporting database on the same instance, you had to choose a global setting that never perfectly suited both.

Since SQL Server 2016, you can use Database Scoped Configurations. This allows you to define a specific MAXDOP for each database within the same instance.

-- Configure MAXDOP only for the current database
USE [MyReportingDatabase];
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;
GO

USE [MyProductionDatabase];
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2;
GO

This granularity is a major asset for server consolidation, allowing you to isolate workload behaviors without multiplying instances.

MAXDOP as a Query Option

Beyond settings at the instance or database level, SQL Server also allows you to specify MAXDOP directly in a query via the OPTION (MAXDOP n) hint.

Use this approach if you want to force behavior for a specific query without modifying the global configuration. You can increase or decrease parallelism relative to the global option.

-- Force use of a single core
SELECT col1, col2
FROM MyTable
WHERE condition = 1
OPTION (MAXDOP 1);

-- Allow up to 4 cores for this query
SELECT col1, col2
FROM MyLargeTable
INNER JOIN OtherTable ON ...
WHERE condition = 1
OPTION (MAXDOP 4);

Use Cases

• Ad-hoc reporting queries: You can temporarily increase MAXDOP for a heavy end-of-month query, even if your database is configured with MAXDOP 2 for OLTP.
• Resolving blocking: Sometimes, forcing MAXDOP 1 on a problematic query eliminates synchronization waits (CXPACKET / CXSYNC_PORT).
• Maintenance and indexes: During index rebuilds or statistics updates, you can control parallelism via MAXDOP to avoid saturating the server. This option is available in many structure modification commands and administrative commands, such as DBCC CHECKDB.

-- Example with index REBUILD
ALTER INDEX IX_MyIndex ON MyTable
REBUILD WITH (MAXDOP = 2, ONLINE = ON);

MAXDOP via Query Store Hints (SQL Server 2022+)

Since SQL Server 2022, Query Store allows you to automatically apply hints to specific queries without modifying their source code. This feature is particularly useful for third-party applications where you cannot modify the queries.

-- Identify the problematic query via Query Store
SELECT q.query_id, qt.query_sql_text, rs.avg_duration
FROM sys.query_store_query q
INNER JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan p ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE qt.query_sql_text LIKE '%MyTable%'
ORDER BY rs.avg_duration DESC;

-- Apply a MAXDOP hint to a specific query
EXEC sys.sp_query_store_set_hints
    @query_id = 12345,
    @query_hints = N'OPTION(MAXDOP 1)';

-- Check applied hints
SELECT query_hint_id, query_id, query_hint_text, last_query_hint_failure_reason
FROM sys.query_store_query_hints;

-- Remove a hint if necessary
EXEC sys.sp_query_store_clear_hints @query_id = 12345;

Advantages of Query Store Hints

• Third-party applications: Control parallelism for queries in applications whose code you don’t control.
• Centralized management: All hints are stored in Query Store, facilitating auditing and documentation.
• Reversible: You can enable or disable a hint without application redeployment.
• Proactive performance: Identify problematic queries via Query Store statistics and apply targeted corrections.

MAXDOP via Resource Governor

Resource Governor allows you to control MAXDOP based on the workload group to which a session belongs. This approach is ideal for isolating different types of workloads on the same instance.

-- Create a resource pool with limited MAXDOP
CREATE RESOURCE POOL PoolReporting
WITH (
    MAX_CPU_PERCENT = 50,
    MAX_DOP = 4  -- Maximum MAXDOP for this pool
);

CREATE RESOURCE POOL PoolOLTP
WITH (
    MAX_CPU_PERCENT = 80,
    MAX_DOP = 2
);

-- Create workload groups
CREATE WORKLOAD GROUP GroupReporting
WITH (IMPORTANCE = MEDIUM)
USING PoolReporting;

CREATE WORKLOAD GROUP GroupOLTP
WITH (IMPORTANCE = HIGH)
USING PoolOLTP;

-- Create a classifier function to route sessions
CREATE FUNCTION dbo.fn_ClassifierMAXDOP()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @WorkloadGroup sysname;

    -- Classify by application name
    IF APP_NAME() LIKE '%Report%'
        SET @WorkloadGroup = 'GroupReporting';
    ELSE IF APP_NAME() LIKE '%Prod%'
        SET @WorkloadGroup = 'GroupOLTP';
    ELSE
        SET @WorkloadGroup = 'default';

    RETURN @WorkloadGroup;
END;
GO

-- Enable Resource Governor with the classifier function
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.fn_ClassifierMAXDOP);

ALTER RESOURCE GOVERNOR RECONFIGURE;

Resource Governor Use Cases

• Workload isolation: Separate OLTP queries (low MAXDOP) from reporting queries (higher MAXDOP) on the same instance.
• Multi-tenant management: Apply different parallelism limits based on client or application.
• User-based control: Limit MAXDOP for certain users or AD groups.
• Protection against runaway queries: Prevent unoptimized queries from monopolizing all server cores.

MAXDOP Hierarchy and Priorities

With all these options, here is the order of priority when multiple configuration levels coexist:

1. OPTION (MAXDOP n) in the query → Absolute priority
2. Query Store Hint → Applies if the query matches the pattern
3. Resource Governor (workload group MAXDOP) → Maximum limit for the workload
4. Database Scoped Configuration → Database-level configuration
5. sp_configure ‘max degree of parallelism’ → Instance global configuration

The final value used will be the most restrictive among these levels. For example, if your instance is set to MAXDOP 8, your database to MAXDOP 4, and your Resource Governor to MAXDOP 2, then 2 will be applied.