Causes and Checks for High CXPACKET Waits in SQL Server

Applicability of This Document

Ready-to-Run Commands

-- 対象: 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);

How to Read the Results

When to Use This

• 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

Possible Causes (Most Likely First)

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.

Diagnostic Steps

1. 1

   Get the top wait types

   Read-only

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

2. 2

   Check instance uptime

   Read-only

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

3. 3

   Measure a delta for the relevant time window

   Read-only

   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

   Low

   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

   Read-only

   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

   Read-only

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

Solutions

Differences by Version and Environment

If This Doesn't Resolve It

• 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.

Basis and Limitations of This Document

FAQ

Questions This Document Covers

• 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

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

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."

Related Services

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