How to Check and Change MAXDOP and Cost Threshold for Parallelism in SQL Server

Applicability of This Document

Ready-to-Run Commands

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: VIEW SERVER STATE または VIEW ANY DEFINITION
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SELECT
    c.configuration_id,
    c.name,
    c.value           AS configured_value,   -- 構成された値
    c.value_in_use    AS running_value,      -- 実際に使われている値
    c.minimum,
    c.maximum,
    c.is_dynamic,     -- 1: 再起動なしで反映される
    c.is_advanced,    -- 1: show advanced options が必要
    c.description
FROM sys.configurations AS c
WHERE c.name IN ('max degree of parallelism', 'cost threshold for parallelism')
ORDER BY c.name;

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: VIEW SERVER STATE
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SELECT
    cpu_count                       AS logical_cpu_count,
    hyperthread_ratio,
    scheduler_count,
    softnuma_configuration_desc     -- SQL Server 2016 以降。それ以前の版では列を外すこと
FROM sys.dm_os_sys_info;

-- NUMA ノードごとのスケジューラ配置
SELECT
    parent_node_id                  AS numa_node_id,
    COUNT(*)                        AS scheduler_count
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'
GROUP BY parent_node_id
ORDER BY parent_node_id;

-- 対象: SQL Server 2008 以降
-- 権限: ALTER SETTINGS（sysadmin / serveradmin 相当）
-- 変更作業: あり（show advanced options の切り替えはサーバー構成の変更）
-- Production 実行: 可能。ただし確認目的なら sys.configurations を推奨
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

EXEC sp_configure 'max degree of parallelism';
EXEC sp_configure 'cost threshold for parallelism';

-- 対象: SQL Server 2008 以降（Amazon RDS ではパラメータグループ経由）
-- 権限: ALTER SETTINGS（sysadmin / serveradmin 相当）
-- 変更作業: あり（サーバー全体の実行プラン選択に影響）
-- Production 実行: 可能。ただし変更前後の測定と切り戻し手順が前提
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

-- 下の値は「例示」であり推奨値ではない。必ず自環境の測定結果に置き換えること
DECLARE @maxdop         int = 4;
DECLARE @cost_threshold int = 50;

EXEC sp_configure 'max degree of parallelism', @maxdop;
RECONFIGURE;

EXEC sp_configure 'cost threshold for parallelism', @cost_threshold;
RECONFIGURE;
GO

-- 反映結果の確認
SELECT name, value, value_in_use
FROM sys.configurations
WHERE name IN ('max degree of parallelism', 'cost threshold for parallelism');

-- 対象: SQL Server 2016 以降 / Azure SQL Database
-- 権限: ALTER ANY DATABASE SCOPED CONFIGURATION
-- 変更作業: あり（対象データベース全体の実行プラン選択に影響）
-- Production 実行: 可能。対象DB全体に効くため影響範囲を確認すること
USE [SampleDB];
GO

-- 現在値の確認（参照のみ）
SELECT configuration_id, name, value, value_for_secondary
FROM sys.database_scoped_configurations
WHERE name = 'MAXDOP';

-- 変更（0 はサーバー設定に従う）
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: 対象オブジェクトへの参照権限
-- 変更作業: あり（このクエリの実行プランのみ変わる）
-- Production 実行: 可能（影響はこのクエリに限定）
SELECT col1, col2
FROM dbo.SampleTable
WHERE col1 > 0
OPTION (MAXDOP 1);

How to Read the Results

When to Use This

• CXPACKET / CXCONSUMER dominate the top of the wait statistics
• Even small queries end up with parallel plans, and only CPU usage goes up
• Overall throughput drops during periods of high concurrency
• The same query has a different execution plan in the test environment versus production

Possible Causes (Most Likely First)

1. 01

   cost threshold for parallelism is still at its default

   The default value of 5 assumes hardware from a very different, much older era. In modern environments, this makes it easy for queries that gain little from parallelism to become candidates for a parallel plan anyway. That said, there is no universal "right" value — it depends on the environment. Decide only after measuring the actual distribution of query costs.

2. 02

   MAXDOP has been left at 0 (unlimited)

   0 means "use every available logical CPU." On a server with many cores, a single query can occupy a large number of threads, squeezing out other work when multiple queries run concurrently.

3. 03

   The server setting does not match the workload's nature

   OLTP-centric processing and analytics-centric processing call for different degrees of parallelism. When both coexist on the same instance, a single server-wide value cannot be fully optimized for either.

4. 04

   Outdated statistics cause the cost estimate to diverge from reality

   If the estimated row count is too high, the estimated cost comes out high enough to cross the threshold and trigger a parallel plan. The fix here is updating statistics, not changing the degree-of-parallelism setting.

Diagnostic Steps

1. 1

   Check the current values

   Read-only

   Check the `value` and `value_in_use` of both settings in `sys.configurations`. Read-only.

2. 2

   Check the hardware configuration

   Read-only

   Check the logical CPU count and the number of schedulers per NUMA node — this is the background needed to decide on a setting.

3. 3

   Measure the weight of parallel waits from wait statistics

   Read-only

   Use a delta for the relevant time window to check the weight of CXPACKET / CXCONSUMER. A cumulative total alone cannot be judged.

4. 4

   Capture the execution plan of an actually slow query

   Low

   Check whether it has a parallel operator and whether row counts are unevenly distributed across threads.

5. 5

   Verify the effect at the query level

   Low

   Run the query with `OPTION (MAXDOP n)` added and check whether response time improves. If it does not improve, changing the server setting will not help either.

6. 6

   Check the freshness of statistics

   Read-only

   Confirm that a gap in the cost estimate is not the cause before proceeding to a setting change.

Solutions

Differences by Version and Environment

If This Doesn't Resolve It

• Cost distribution of queries getting parallel plans

  Aggregate estimated costs from the plan cache to see how many queries sit near the threshold.

• Per-thread row counts in execution plans

  Check whether row counts are unevenly distributed across parallel threads. If they are, raising the degree of parallelism will have limited effect.

• Memory grant status

  Parallel queries request larger memory grants. Check whether `RESOURCE_SEMAPHORE` waits co-occur.

• Availability group secondary settings

  Check whether `value_for_secondary` in the database-scoped configuration differs from the primary's setting.

Basis and Limitations of This Document

FAQ

Questions This Document Covers

• I want to check the current value of MAXDOP
• I want to change the degree of parallelism per database
• I want to know how to change MAXDOP on RDS

What the Risk Labels Mean

• Read-onlyDoes not change data or settings.
• LowImpact is limited, but permissions and load should be checked.
• MediumMay affect performance, locking, or cost.
• HighMay cause an outage, data loss, or require recovery work.
• Expert Review RequiredRequires a separate review before applying to production.

GIIP's Scope of Support

A degree-of-parallelism setting is not something you decide once and forget. As data volume grows, queries get added, and instance sizes change, the right value shifts too. At GIIP, we keep the setting value itself alongside the wait events and response times observed under that setting, on the same timeline, so we can trace exactly what changed before and after a change. Broad-impact operations like changing a setting are never run automatically by an AI agent — measurement and proposals are automated, but the decision to apply and the approval remain a human responsibility.

Related Services

• データベース性能チューニング
• AWSマネージド運用の対応範囲