Causes and Checks for High CXPACKET Waits in SQL Server

この文書の適用条件

そのまま実行できるコマンド

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: VIEW SERVER STATE
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SELECT TOP (20)
    ws.wait_type,
    ws.waiting_tasks_count,
    ws.wait_time_ms,
    ws.wait_time_ms - ws.signal_wait_time_ms AS resource_wait_ms,
    ws.signal_wait_time_ms,
    ws.max_wait_time_ms,
    CAST(100.0 * ws.wait_time_ms
         / NULLIF(SUM(ws.wait_time_ms) OVER (), 0) AS decimal(5, 2)) AS pct_of_total
FROM sys.dm_os_wait_stats AS ws
WHERE ws.waiting_tasks_count > 0
  -- 常時発生するアイドル・バックグラウンド待機を除外する
  AND ws.wait_type NOT IN (
        'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
        'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
        'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',
        'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT',
        'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
        'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'ONDEMAND_TASK_QUEUE',
        'BROKER_EVENTHANDLER', 'SLEEP_BPOOL_FLUSH', 'DIRTY_PAGE_POLL',
        'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'SP_SERVER_DIAGNOSTICS_SLEEP',
        'QDS_ASYNC_QUEUE', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION'
      )
ORDER BY ws.wait_time_ms DESC;

-- 対象: SQL Server 2012 以降（sqlserver_start_time 列）
-- 権限: VIEW SERVER STATE
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SELECT
    sqlserver_start_time,
    DATEDIFF(HOUR, sqlserver_start_time, GETDATE()) AS uptime_hours
FROM sys.dm_os_sys_info;

-- 対象: SQL Server 2008 以降（dop 列は SQL Server 2016 以降）
-- 権限: VIEW SERVER STATE
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SELECT
    wt.session_id,
    wt.exec_context_id,
    wt.wait_type,
    wt.wait_duration_ms,
    wt.blocking_session_id,
    wt.blocking_exec_context_id,
    wt.resource_description,
    r.status,
    r.command,
    r.dop,                       -- SQL Server 2016 以降。それ以前の版では列を外すこと
    r.total_elapsed_time,
    txt.text                     AS batch_text
FROM sys.dm_os_waiting_tasks AS wt
LEFT JOIN sys.dm_exec_requests AS r
       ON wt.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE wt.wait_type LIKE 'CX%'
ORDER BY wt.session_id, wt.exec_context_id;

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: VIEW SERVER STATE
-- 変更作業: なし（セッションスコープの一時テーブルのみ）
-- Production 実行: 可能
-- 1) 計測開始時点のスナップショットを取る
SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
INTO #wait_snapshot
FROM sys.dm_os_wait_stats;

-- 2) 計測したい時間だけ待つ（例: 10分）
WAITFOR DELAY '00:10:00';

-- 3) 差分を取る
SELECT TOP (20)
    w.wait_type,
    w.waiting_tasks_count - s.waiting_tasks_count AS delta_tasks,
    w.wait_time_ms        - s.wait_time_ms        AS delta_wait_ms,
    w.signal_wait_time_ms - s.signal_wait_time_ms AS delta_signal_ms
FROM sys.dm_os_wait_stats AS w
INNER JOIN #wait_snapshot AS s
        ON w.wait_type = s.wait_type
WHERE w.wait_time_ms - s.wait_time_ms > 0
ORDER BY delta_wait_ms DESC;

DROP TABLE #wait_snapshot;

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: サーバーレベルの権限（sysadmin 相当）
-- 変更作業: あり（インスタンス全体の待機統計をリセット）
-- Production 実行: 推奨しない。差分計測で代替できないか先に検討すること
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

結果の読み方

こういう状況で使います

• When wait statistics are aggregated, CXPACKET is consistently near the top
• CPU usage is high, but individual query throughput does not improve
• The same query takes wildly different amounts of time from one run to the next
• Overall responsiveness degrades specifically during periods of high concurrency

考えられる原因（可能性の高い順）

1. 01

   This is not actually a problem (parallel execution is working as intended)

   CXPACKET necessarily occurs whenever tasks in a parallel query synchronize with each other. In systems with a lot of analytical queries, it is normal for it to rank high, and ranking high by itself does not indicate a problem. Rule this out first.

2. 02

   Cost Threshold for Parallelism is too low, causing even small queries to go parallel

   The default value of 5 dates back to a very different era of hardware. In many environments, leaving it at this value causes queries that gain little from parallelism to get parallel plans anyway, adding coordination overhead with little benefit. That said, the right value depends on the workload and should not be changed without measurement.

3. 03

   Workload is unevenly distributed across parallel workers (skew)

   In partitioned processing, if one worker handles a disproportionate number of rows, the other workers sit idle waiting. You can confirm this from the row-count distribution across threads in the actual execution plan.

4. 04

   Outdated statistics are causing an unnecessarily large parallel plan to be chosen

   If the estimated row count is larger than the actual count, the estimated cost comes out higher, leading to a parallel plan. The fix here is updating statistics, not changing the degree of parallelism.

5. 05

   Missing indexes are causing a large scan to be parallelized

   Without a suitable index, the query scans the entire table, and that scan is parallelized. Redesigning the index can eliminate the need for parallel execution altogether.

6. 06

   The CPU is saturated

   If the share of `signal_wait_time_ms` is high, what's being waited on is not a resource but CPU scheduling order. Lowering the degree of parallelism can help, but the root cause is insufficient CPU or query inefficiency.

確認手順

1. 1

   Get the top wait types

   参照のみ

   Check the top 20 by cumulative value to understand where CXPACKET / CXCONSUMER rank.

2. 2

   Check instance uptime

   参照のみ

   Cumulative values scale with uptime. Evaluate them alongside `sqlserver_start_time`.

3. 3

   Measure a delta for the relevant time window

   参照のみ

   Take a snapshot delta during the window when the problem occurs to produce wait trends specific to that window.

4. 4

   Identify an actually slow query

   低

   If no slow query exists, no action is needed even if CXPACKET ranks high. Capture the slow query's execution plan before proceeding.

5. 5

   Observe real-time parallel waits

   参照のみ

   Check `CX%` waits with `sys.dm_os_waiting_tasks` and see whether the coordinator task (`exec_context_id = 0`) is waiting for a long time.

6. 6

   Check statistics and indexes

   参照のみ

   Check for a gap between estimated and actual row counts, and for any large scans. Only touch the degree-of-parallelism setting after this.

対応方法

バージョン・環境による違い

これで解決しない場合に確認すること

• Row-count distribution across threads in the execution plan

  Check the actual execution plan for a significant imbalance in row counts across parallel threads — an imbalance indicates skew.

• CPU usage and scheduler state

  Check `runnable_tasks_count` in `sys.dm_os_schedulers` to see whether the CPU run queue is backing up.

• PAGEIOLATCH-family waits

  If I/O waits also rank high alongside CXPACKET, the root cause may lie on the storage side.

• Memory grant waits (RESOURCE_SEMAPHORE)

  Parallel queries request larger memory grants. If this wait co-occurs, memory also needs to be examined.

この文書の根拠と限界

よくある質問

この文書がカバーする質問

• I want to know whether CXPACKET ranking high among waits is abnormal
• I want to know how to read wait events
• I want to check parallel query waits while they are happening

リスク表示の意味

• 参照のみデータと設定を変更しません。
• 低影響は限定的ですが、権限と負荷の確認が必要です。
• 中性能・ロック・コストに影響する可能性があります。
• 高障害・データ損失・復旧作業が発生する可能性があります。
• 専門家レビュー必須本番適用前に別途レビューが必須です。

GIIPの対応範囲

Wait events like CXPACKET are a metric best judged not by a single snapshot but by "how does this compare to normal." At GIIP, wait statistics are periodically captured as snapshots and accumulated in a form that supports comparing deltas across time windows. An AI agent detects deviations from the normal pattern and escalates to a human only when tied to an actually slowed-down query, avoiding number-first judgments like "CXPACKET ranks high, so lower the degree of parallelism."

関連サービス

• データベース性能チューニング
• 24時間監視の対象を確認する