SQL Server에서 CXPACKET 대기가 많을 때의 원인과 확인 방법

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