SQL Server MAXDOP: Why the Installation Default Is Just the Starting Point

---

A production SQL Server is built. The DBA sets MAXDOP to 8 at installation on an 8-core server, exactly following Microsoft’s setup guidance. The server goes live. Data loads, applications connect, workloads grow. Weeks later, daily health checks start flagging parallelism-related wait statistics. The evidence accumulates over a month: BPSORT, CXPACKET, CXSYNC_PORT waits are all elevated. Performance monitoring shows CPU consistently saturated.

When the issue is raised, the DBA responds that MAXDOP is set according to Microsoft best practice at installation and that wait statistics show zero parallelism bottleneck. Both statements reference real Microsoft guidance. Both miss the same critical point: the same Microsoft documentation page that governs the installation default also contains post-installation tuning guidance recommending a lower value once the production workload is known. The install default is a starting point, not a permanent configuration decision.

This article explains what MAXDOP does, why the installation default is correct procedure, exactly what Microsoft says about tuning it after load, how to read the wait statistics that tell you tuning is needed, and how to calculate the right value for your hardware. Every fact in this article is directly sourced from Microsoft’s official documentation.

1 What MAXDOP Does and Why It Matters Beginner

MAXDOP stands for Maximum Degree of Parallelism. It controls the maximum number of CPU cores SQL Server can use to execute a single query in parallel. When SQL Server evaluates a query and determines that parallel execution would be beneficial, it splits the query into multiple threads and runs them simultaneously across multiple processors. MAXDOP caps how many processors can be used for that parallel execution.

The default value at fresh installation is 0, which means SQL Server can use all available processors for a single query. On a server with 8 cores, MAXDOP 0 allows one query to consume all 8 cores simultaneously. On a 32-core server, one query could consume all 32 cores.

Parallelism is not inherently good or bad. It depends entirely on the workload. For a single large analytical query running on a quiet server, high parallelism can cut execution time dramatically. For an OLTP server handling hundreds of small concurrent transactions, high parallelism means multiple queries each spawning many threads, competing for CPU, creating thread synchronization overhead, and producing exactly the wait statistics that signal a problem.

2 What Happens at Installation: Why the Default Is Set the Way It Is Beginner

When you install SQL Server, the setup wizard sets MAXDOP based on a straightforward hardware rule. On SQL Server 2019 and later, the setup page explicitly shows you the recommended value based on the server’s logical processor count and asks you to accept or change it. On earlier versions, the setup applies the rule silently.

The installation rule is: set MAXDOP to the number of logical processors, up to a maximum of 8. On an 8-core server, MAXDOP is set to 8. On a 4-core server, MAXDOP is set to 4. On a 32-core server, MAXDOP is set to 8 (capped at 8 regardless of core count above that threshold).

This is correct procedure. Setting MAXDOP at installation is better than leaving it at 0. It establishes a reasonable upper bound based on the hardware before any workload is known. It prevents the worst-case scenario where a single runaway parallel query consumes every available core on first use.

3 The Part of the Documentation Most DBAs Miss Intermediate

The Microsoft documentation page for configuring MAXDOP contains two distinct sections that are often read separately or only partially. The first section covers the installation guidelines: the hardware-based rules for setting MAXDOP at setup time. The second section covers post-installation tuning guidance. Both sections are on the same page. Both are official Microsoft guidance. They are not in conflict. They describe two different phases of the same configuration lifecycle.

The post-installation guidance for systems with 8 or fewer logical processors states: set MAXDOP to N or lower, where the recommended value is half the logical processor count. On an 8-core server, the installation default sets MAXDOP to 8. The post-installation tuning guidance recommends evaluating whether MAXDOP 4 (half of 8) is more appropriate for the actual production workload.

The distinction between “install-time default” and “recommended post-install tuning value” is the source of most MAXDOP misunderstandings. A DBA who applies the installation guidance correctly and then does not revisit MAXDOP after the production workload is established is following half of the documented process. The installation step is correct. The tuning step after load is equally documented and equally required.

4 When the Install Default Is Not Enough: A Common Production Pattern Intermediate

The scenario described here is one production DBAs encounter regularly. A server running MAXDOP 8 on an 8-core system begins showing parallelism-related wait statistics in daily health checks. Over several weeks, report after report flags the same pattern. The response is that MAXDOP is set correctly per Microsoft best practice at installation and that wait statistics show no parallelism bottleneck. Both claims reference real guidance. Both miss the post-install tuning step.

The response during that period was that MAXDOP was configured correctly per Microsoft best practice at installation and that wait statistics showed no parallelism bottleneck. The wait statistics data that accompanied the response told a different story.

Wait Type	Category	Waiting Tasks	Wait Time (ms)	Signal Wait (ms)	Assessment
BPSORT	Parallelism	18,400,000+	32,000,000+	3,200,000+	Critical

BPSORT is a parallelism wait type generated when parallel sort operations spill to disk. It is not a background or benign wait. It represents active query operations that are waiting because parallel sort threads are competing for resources and overflowing into disk I/O. Tens of millions of waiting tasks with billions of milliseconds of accumulated wait time is a significant parallelism load, not zero.

Beyond BPSORT, the broader wait statistics picture typically shows additional parallelism wait types accumulating together. CXPACKET measures thread synchronization overhead in parallel plans. CXSYNC_PORT measures synchronization between parallel threads at exchange operators. When both are elevated alongside BPSORT, and SOS_SCHEDULER_YIELD signal wait is consuming a very high percentage of total wait time, the server is CPU-saturated and parallelism is amplifying the problem rather than helping it.

5 How to Read Parallelism Wait Statistics Correctly Intermediate

The wait statistics that signal a MAXDOP tuning opportunity are specific and well-documented. Understanding what each one means allows you to make a data-driven decision about whether to tune and in which direction.

The Key Parallelism Wait Types

Wait Type	What It Means	Tuning Signal
CXPACKET	Threads in a parallel plan waiting at an exchange operator for other threads to catch up. One thread finishes its portion faster than others and waits.	High values with unequal thread distribution suggest MAXDOP is too high for the workload pattern.
CXSYNC_PORT	Synchronization wait at exchange port operators between parallel threads. A refinement of CXPACKET tracking introduced in SQL Server 2016.	Elevated alongside CXPACKET confirms parallelism thread synchronization overhead.
BPSORT	Parallel sort operations spilling to disk because parallel threads cannot complete their sort operations in memory.	High values indicate parallel sort operations are overloading the system. Reducing MAXDOP reduces the number of sort threads competing simultaneously.
CXCONSUMER	Consumer threads in a parallel plan waiting for the producer thread to provide rows. Introduced in SQL Server 2016 to provide a benign subset of CXPACKET.	High CXCONSUMER with low CXPACKET is usually benign. High both together confirms active parallelism overhead.
SOS_SCHEDULER_YIELD	Threads voluntarily yielding the scheduler to allow other threads to run. High signal wait on this type means CPU is fully saturated and threads are queuing.	Signal wait near 100% of total wait time means CPU is a hard bottleneck. Parallelism amplifies this by multiplying thread count per query.

-- Query current wait statistics to identify parallelism-related waits
-- Run during peak hours to get a representative picture

SELECT
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms          AS resource_wait_ms,
    ROUND(100.0 * wait_time_ms
        / SUM(wait_time_ms) OVER (), 2)         AS pct_total_wait,
    CASE
        WHEN wait_type IN ('CXPACKET','CXSYNC_PORT','CXCONSUMER','BPSORT')
        THEN 'Parallelism'
        WHEN wait_type = 'SOS_SCHEDULER_YIELD'
        THEN 'CPU Pressure'
        ELSE 'Other'
    END                                         AS category
FROM sys.dm_os_wait_stats
WHERE wait_type IN (
    'CXPACKET', 'CXSYNC_PORT', 'CXCONSUMER', 'BPSORT',
    'SOS_SCHEDULER_YIELD', 'THREADPOOL'
)
AND   waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;

-- Compare parallelism waits as a percentage of total waits:
SELECT
    SUM(CASE WHEN wait_type IN ('CXPACKET','CXSYNC_PORT','CXCONSUMER','BPSORT')
             THEN wait_time_ms ELSE 0 END) AS parallelism_wait_ms,
    SUM(wait_time_ms)                      AS total_wait_ms,
    ROUND(100.0 *
        SUM(CASE WHEN wait_type IN ('CXPACKET','CXSYNC_PORT','CXCONSUMER','BPSORT')
                 THEN wait_time_ms ELSE 0 END)
        / NULLIF(SUM(wait_time_ms), 0), 2) AS parallelism_pct_of_total
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    -- Exclude benign background waits that inflate total
    'SLEEP_TASK','BROKER_TO_FLUSH','BROKER_TASK_STOP',
    'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE',
    'FT_IFTS_SCHEDULER_IDLE_WAIT','HADR_WORK_QUEUE',
    'LAZYWRITER_SLEEP','LOGMGR_QUEUE','ONDEMAND_TASK_QUEUE',
    'REQUEST_FOR_DEADLOCK_SEARCH','RESOURCE_QUEUE',
    'SERVER_IDLE_CHECK','SLEEP_DBSTARTUP','SLEEP_DCOMSTARTUP',
    'SLEEP_MASTERDBREADY','SLEEP_MASTERMDREADY','SLEEP_MASTERUPGRADED',
    'SLEEP_MSDBSTARTUP','SLEEP_SYSTEMTASK','SLEEP_TEMPDBSTARTUP',
    'SNI_HTTP_ACCEPT','SP_SERVER_DIAGNOSTICS_SLEEP','SQLTRACE_BUFFER_FLUSH',
    'WAITFOR','WAIT_XTP_OFFLINE_CKPT_NEW_LOG','XE_DISPATCHER_WAIT',
    'XE_TIMER_EVENT','BROKER_EVENTHANDLER','CHECKPOINT_QUEUE',
    'DBMIRROR_EVENTS_QUEUE','SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
);

6 Calculating the Right MAXDOP for Your Hardware Intermediate

The correct starting value for MAXDOP tuning is derived from your server’s NUMA topology. NUMA (Non-Uniform Memory Access) is the hardware architecture that groups processors with their local memory. SQL Server is NUMA-aware and performs best when parallel query threads stay within a single NUMA node rather than crossing NUMA boundaries.

-- Step 1: Understand your server's hardware topology
-- Run this before calculating any MAXDOP recommendation

SELECT
    cpu_count                       AS logical_cpu_count,
    hyperthread_ratio               AS logical_per_physical,
    cpu_count / hyperthread_ratio   AS physical_core_count,
    socket_count,
    numa_node_count,
    cpu_count / numa_node_count     AS logical_cpus_per_numa_node,
    softnuma_configuration_desc
FROM sys.dm_os_sys_info;

-- Step 2: Check NUMA node layout in detail
SELECT
    memory_node_id                  AS numa_node_id,
    online_scheduler_count          AS schedulers_on_node,
    active_worker_count             AS active_workers
FROM sys.dm_os_nodes
WHERE memory_node_id != 64;   -- 64 is the DAC node, not a real NUMA node

-- Step 3: Check the current MAXDOP setting
EXEC sp_configure 'max degree of parallelism';
-- run_value = current active setting
-- config_value = configured value (may differ if RECONFIGURE not run)

The Microsoft MAXDOP Recommendation Table: SQL Server 2016 and Later

These guidelines apply to SQL Server 2016 through SQL Server 2025. This version introduced soft-NUMA which changed the threshold for multiple NUMA node guidance from 8 processors per node to 16.

Hardware Configuration	Microsoft Recommended Starting Value
Single processor (1 logical CPU)	MAXDOP = 1 (disable parallelism, no benefit possible)
Single NUMA node, 8 or fewer logical processors	MAXDOP at or under the number of logical processors. For an 8-core server this means MAXDOP 8 at install — then evaluate reducing after workload is known.
Single NUMA node, more than 8 logical processors	MAXDOP = 8 (cap at 8 regardless of higher core count)
Multiple NUMA nodes, 16 or fewer logical processors per NUMA node	MAXDOP at or under the logical processors per NUMA node
Multiple NUMA nodes, more than 16 logical processors per NUMA node	MAXDOP = half the logical processors per NUMA node, maximum 16
OLTP workload (many small concurrent transactions)	Test MAXDOP = 1 to eliminate parallelism entirely. Many OLTP systems benefit from serial execution.

SQL Server 2008 Through SQL Server 2014

The guidance for older versions uses a lower threshold for multiple NUMA node configurations. The single NUMA node rules are the same.

Hardware Configuration	Microsoft Recommended Starting Value
Single NUMA node, 8 or fewer logical processors	MAXDOP at or under the number of logical processors
Single NUMA node, more than 8 logical processors	MAXDOP = 8
Multiple NUMA nodes, 8 or fewer logical processors per NUMA node	MAXDOP at or under the logical processors per NUMA node
Multiple NUMA nodes, more than 8 logical processors per NUMA node	MAXDOP = 8

-- Automated MAXDOP recommendation script
-- Based directly on Microsoft documentation guidance
-- Returns the recommended starting value for your hardware

DECLARE
    @logical_cpu_count   INT,
    @numa_node_count     INT,
    @cpus_per_numa       INT,
    @recommended_maxdop  INT;

SELECT
    @logical_cpu_count = cpu_count,
    @numa_node_count   = numa_node_count
FROM sys.dm_os_sys_info;

SET @cpus_per_numa = @logical_cpu_count / @numa_node_count;

-- Apply Microsoft's documented rules
SET @recommended_maxdop =
    CASE
        -- Single processor: no parallelism benefit
        WHEN @logical_cpu_count = 1
            THEN 1
        -- Single NUMA node, 8 or fewer CPUs: use CPU count, consider halving
        WHEN @numa_node_count = 1 AND @logical_cpu_count <= 8
            THEN @logical_cpu_count
        -- Single NUMA node, more than 8 CPUs: cap at 8
        WHEN @numa_node_count = 1 AND @logical_cpu_count > 8
            THEN 8
        -- Multiple NUMA nodes, 16 or fewer per node: use CPUs per NUMA node
        WHEN @numa_node_count > 1 AND @cpus_per_numa <= 16
            THEN @cpus_per_numa
        -- Multiple NUMA nodes, more than 16 per node: half of CPUs per node, max 16
        WHEN @numa_node_count > 1 AND @cpus_per_numa > 16
            THEN CASE WHEN (@cpus_per_numa / 2) > 16 THEN 16
                      ELSE @cpus_per_numa / 2 END
        ELSE 8
    END;

SELECT
    @logical_cpu_count                      AS logical_cpus,
    @numa_node_count                        AS numa_nodes,
    @cpus_per_numa                          AS cpus_per_numa_node,
    @recommended_maxdop                     AS recommended_starting_maxdop,
    @recommended_maxdop / 2                 AS conservative_oltp_starting_point,
    (SELECT value_in_use FROM sys.configurations
     WHERE name = 'max degree of parallelism') AS current_maxdop,
    CASE
        WHEN (SELECT value_in_use FROM sys.configurations
              WHERE name = 'max degree of parallelism') > @recommended_maxdop
        THEN 'Current MAXDOP exceeds recommendation -- consider tuning'
        WHEN (SELECT value_in_use FROM sys.configurations
              WHERE name = 'max degree of parallelism') = @recommended_maxdop
        THEN 'Current MAXDOP matches hardware recommendation'
        ELSE 'Current MAXDOP is below hardware recommendation'
    END                                     AS assessment;

7 The Evidence-Based Tuning Process Intermediate

Changing MAXDOP on a production server is a configuration change that affects every parallel query on the instance. It should never be done based on a single data point or a general recommendation alone. The correct process establishes a baseline, makes an incremental change, and measures the result against the baseline before committing.

To calculate your recommended MAXDOP value based on your server hardware and live wait statistics, use the free SQLYARD MAXDOP Advisor — input your core count, NUMA nodes, workload type, and CXPACKET data and get a runtime-informed recommendation with the T-SQL to apply it.

Step 1: Establish a Baseline

-- Capture current wait statistics snapshot as baseline
-- Run during a representative peak period and save the results

SELECT
    GETDATE()               AS snapshot_time,
    'baseline'              AS label,
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms
INTO dbo.WaitStatsBaseline
FROM sys.dm_os_wait_stats
WHERE wait_type IN (
    'CXPACKET','CXSYNC_PORT','CXCONSUMER','BPSORT',
    'SOS_SCHEDULER_YIELD','THREADPOOL'
)
AND waiting_tasks_count > 0;

-- Also capture current MAXDOP for the baseline record
EXEC sp_configure 'max degree of parallelism';

Step 2: Make the Incremental Change

-- Change MAXDOP incrementally -- do not jump from 8 to 1 directly
-- Start by halving: MAXDOP 8 → MAXDOP 4 on an 8-core server
-- Monitor for one full business cycle (minimum one week) before further changes

-- Check current value first:
EXEC sp_configure 'max degree of parallelism';

-- Apply the change:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

EXEC sp_configure 'max degree of parallelism', 4;  -- adjust to your target value
RECONFIGURE WITH OVERRIDE;
GO

-- Verify the change is active:
EXEC sp_configure 'max degree of parallelism';
-- config_value and run_value should both show 4

-- Note: RECONFIGURE takes effect immediately without a service restart
-- Existing cached query plans may still use the old parallelism degree
-- until they are recompiled on next execution

Step 3: Clear the Wait Stats Accumulation and Monitor

-- Wait stats accumulate since last restart
-- To measure the impact of the MAXDOP change cleanly,
-- reset wait stats immediately after the change and measure from zero

-- WARNING: this clears ALL accumulated wait statistics on the instance
-- Only do this in a maintenance window or after hours, not during peak load
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);

-- After one full business cycle, compare the new wait pattern
-- to the baseline you captured before the change

-- Check whether parallelism waits have decreased:
SELECT
    w.wait_type,
    w.waiting_tasks_count,
    w.wait_time_ms,
    b.wait_time_ms                                  AS baseline_wait_ms,
    w.wait_time_ms - b.wait_time_ms                 AS delta_ms,
    CASE WHEN b.wait_time_ms > 0
         THEN ROUND(100.0 * (w.wait_time_ms - b.wait_time_ms)
              / b.wait_time_ms, 1)
         ELSE NULL
    END                                             AS pct_change
FROM sys.dm_os_wait_stats w
LEFT JOIN dbo.WaitStatsBaseline b ON b.wait_type = w.wait_type
WHERE w.wait_type IN ('CXPACKET','CXSYNC_PORT','BPSORT','SOS_SCHEDULER_YIELD')
ORDER BY w.wait_time_ms DESC;

8 Setting MAXDOP at Server, Database, and Query Level Intermediate

MAXDOP has three scopes in SQL Server, each overriding the one above it. Understanding all three allows you to tune precisely without affecting workloads that are performing correctly.

-- SCOPE 1: Server level (applies to all databases unless overridden)
-- This is what sp_configure controls
EXEC sp_configure 'max degree of parallelism', 4;
RECONFIGURE WITH OVERRIDE;

-- SCOPE 2: Database level (overrides server level for that database only)
-- Available since SQL Server 2016 via ALTER DATABASE SCOPED CONFIGURATION
-- Useful when different databases have different workload types on the same instance

-- Set MAXDOP for a specific database (OLAP database can use higher value)
ALTER DATABASE [YourOLAPDatabase]
    SET MAXDOP = 8;

-- Set MAXDOP for a specific database (OLTP database benefits from lower value)
ALTER DATABASE [YourOLTPDatabase]
    SET MAXDOP = 2;

-- Reset a database back to inheriting the server-level setting
ALTER DATABASE [YourDatabase]
    SET MAXDOP = 0;   -- 0 at database scope = use server-level setting

-- Check current database-scoped MAXDOP settings across all databases:
SELECT
    d.name                              AS database_name,
    c.value                             AS db_scoped_maxdop,
    CASE WHEN c.value = 0
         THEN 'Inherits server-level setting'
         ELSE 'Override: MAXDOP ' + CAST(c.value AS VARCHAR)
    END                                 AS effective_setting
FROM sys.databases d
CROSS APPLY sys.dm_exec_query_stats qs  -- just to get a join
JOIN sys.database_scoped_configurations c
    ON c.name = 'MAXDOP'
    AND DB_ID() = DB_ID(d.name)         -- simplified; query sys.databases directly
WHERE d.database_id > 4                 -- user databases only
ORDER BY d.name;

-- Cleaner version:
SELECT
    database_id,
    name,
    -- database-scoped MAXDOP requires switching context or using dm_exec_query_stats
    -- Check via sys.dm_exec_query_stats or SSMS Database Properties > Options
    name AS database_name
FROM sys.databases
WHERE database_id > 4;

-- SCOPE 3: Query level (overrides everything for that specific query)
-- Use when one specific query needs different parallelism than the rest of the workload

-- Force a specific query to run serially (no parallelism at all):
SELECT OrderID, CustomerID, TotalAmount
FROM dbo.Orders
WHERE OrderDate > '2025-01-01'
OPTION (MAXDOP 1);

-- Allow a specific query to use more parallelism than the server default:
SELECT CustomerID, SUM(TotalAmount) AS Revenue
FROM dbo.Orders
GROUP BY CustomerID
OPTION (MAXDOP 8);

-- This is useful when the server MAXDOP is set low for OLTP
-- but one specific analytical query legitimately needs more parallel threads

9 Cost Threshold for Parallelism: The Setting That Works With MAXDOP Intermediate

MAXDOP controls how many threads a parallel query can use. Cost Threshold for Parallelism (CTFP) controls which queries are even considered for parallel execution. These two settings work together and tuning one without the other often produces incomplete results.

SQL Server estimates a cost for every query plan in abstract cost units. When a query’s estimated cost exceeds the CTFP value, the optimizer considers a parallel plan. When it falls below the threshold, serial execution is used regardless of MAXDOP. The default CTFP value is 5, which is extremely low by modern standards. On a server with fast storage and ample memory, almost every non-trivial query exceeds a cost of 5, meaning almost everything is eligible for parallelism.

-- Check current Cost Threshold for Parallelism:
EXEC sp_configure 'cost threshold for parallelism';
-- Default: 5 (very low -- most queries qualify for parallelism)
-- Recommended starting point for OLTP: 50
-- Tune upward if CXPACKET waits remain elevated after MAXDOP tuning

-- Raise Cost Threshold for Parallelism:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE WITH OVERRIDE;
GO

-- The combined effect of MAXDOP + CTFP tuning:
-- CTFP filters out small queries that should never go parallel
-- MAXDOP limits how many threads the remaining parallel queries can use
-- Tuning both together reduces parallelism overhead more effectively
-- than tuning either one alone

10 DOP Feedback in SQL Server 2022 and 2025 Advanced

SQL Server 2022 introduced Degree of Parallelism (DOP) Feedback as part of the Intelligent Query Processing feature family. It is also available in Azure SQL Database and SQL Database in Microsoft Fabric. DOP Feedback automatically identifies queries where parallelism is inefficient, uses Query Store to track the problem, and progressively reduces the effective MAXDOP for those specific queries without requiring manual intervention or changes to the server-level MAXDOP setting.

DOP Feedback operates at the per-query level. It monitors queries that use parallelism and detects when the parallel execution is producing more overhead (thread synchronization wait time) than benefit (reduced elapsed time). When it identifies a query fitting that pattern, it persists a lower effective DOP for that query in Query Store and applies it on subsequent executions. The adjustment is conservative and incremental, reducing DOP one step at a time and verifying improvement before reducing further.

-- DOP Feedback requires Query Store to be enabled
-- Check Query Store is on:
SELECT name, is_query_store_on
FROM sys.databases
WHERE name = DB_NAME();

-- Enable Query Store if not already on (SQL Server 2022+):
ALTER DATABASE [YourDatabase]
SET QUERY_STORE = ON;

-- DOP Feedback is enabled by default in SQL Server 2022 and 2025
-- when the database compatibility level is 160 or higher

-- Check database compatibility level:
SELECT name, compatibility_level
FROM sys.databases
WHERE name = DB_NAME();
-- Must be 160 (SQL Server 2022) or higher for DOP Feedback to activate

-- Set compatibility level to enable DOP Feedback:
ALTER DATABASE [YourDatabase]
SET COMPATIBILITY_LEVEL = 160;

-- Monitor DOP Feedback activity via Query Store:
SELECT
    q.query_id,
    qt.query_sql_text,
    f.feature_desc,
    f.feedback_data,
    f.state_desc
FROM sys.query_store_query            q
JOIN sys.query_store_query_text       qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan_feedback    f  ON q.query_id      = f.query_id
WHERE f.feature_desc = 'DopFeedback'
ORDER BY q.query_id;

11 The MAXDOP Decision Framework Beginner

Use this framework at every stage of a server’s lifecycle from installation through ongoing production monitoring.

At Installation

• Accept the SQL Server setup recommendation or apply the hardware rule manually: logical processor count up to 8, then cap at 8.
• Document the value chosen and the hardware it was based on.
• Note that this is a starting point pending workload tuning, not a final value.

After Initial Data Load and First Production Workload Period

• Run the NUMA topology query and confirm the hardware recommendation still applies.
• Capture a wait statistics baseline during peak hours.
• If parallelism waits (CXPACKET, CXSYNC_PORT, BPSORT) are in the top 5 waits by accumulated time, a tuning review is warranted.
• Consider raising Cost Threshold for Parallelism to 50 as the first step before reducing MAXDOP.
• If parallelism waits remain elevated, reduce MAXDOP by half and monitor for one full business cycle.

Ongoing Monthly Health Checks

• Include parallelism wait percentages in every health check report.
• Track the trend over time. A single elevated reading may not require action. Nineteen consecutive elevated readings over a month require a review.
• After any significant increase in data volume or query pattern change, revisit the MAXDOP value.
• On SQL Server 2022 and 2025, review DOP Feedback activity in Query Store monthly.

References

• Microsoft Docs: Configure the max degree of parallelism (MAXDOP) — SQL Server
• Microsoft Docs: Configure MAXDOP in Azure SQL Database
• Microsoft Docs: Degree of Parallelism (DOP) Feedback
• Microsoft Docs: Query Store Overview
• Microsoft Docs: sys.dm_os_wait_stats
• Microsoft Docs: sys.dm_os_sys_info (NUMA topology)
• Erik Darling: Thoughts On Setting MAXDOP Correctly in SQL Server
• Stedman Solutions: Optimize SQL Server with Max Degree of Parallelism (December 2024)
• SQLYARD: SQL Server Performance Tuning Complete Guide
• SQLYARD: How to Read SQL Server Execution Plans
• SQLYARD: Understanding Parameter Sniffing in SQL Server