PART 03 — Query Store による取得とセクション定義

-- データベース単位で有効化
ALTER DATABASE [YourDatabase]
SET QUERY_STORE = ON (
    OPERATION_MODE         = READ_WRITE,  -- READ_WRITE: 収集有効 / READ_ONLY: 収集停止・参照のみ
    CLEANUP_POLICY         = (STALE_QUERY_THRESHOLD_DAYS = 30),  -- データ保持日数
    DATA_FLUSH_INTERVAL_SECONDS = 900,    -- メモリ→ストレージへのフラッシュ間隔（秒）
    INTERVAL_LENGTH_MINUTES = 60,         -- 統計集計インターバル（分）
    MAX_STORAGE_SIZE_MB    = 1000,        -- Query Store が使用する最大ストレージ (MB)
    QUERY_CAPTURE_MODE     = AUTO,        -- AUTO: 重要クエリのみ / ALL: 全クエリ
    SIZE_BASED_CLEANUP_MODE = AUTO,       -- ストレージ上限到達時の自動クリーンアップ
    MAX_PLANS_PER_QUERY    = 200,         -- クエリ1件に保持するプランの最大数
    WAIT_STATS_CAPTURE_MODE = ON          -- 待機統計収集 (SQL Server 2017+)
);
GO

-- 有効化の確認
SELECT name, is_query_store_on
FROM sys.databases
WHERE name = 'YourDatabase';

-- 特定期間のTop SQL by 合計経過時間
-- @start_time / @end_time で分析期間を指定
DECLARE @start_time DATETIME2 = '2026-05-29 09:00:00';
DECLARE @end_time   DATETIME2 = '2026-05-29 12:00:00';

SELECT TOP 20
    qt.query_sql_text,
    q.query_id,
    p.plan_id,
    rs.count_executions,
    rs.avg_duration        / 1000.0   AS avg_duration_ms,
    rs.total_duration      / 1000.0   AS total_duration_ms,
    rs.avg_cpu_time        / 1000.0   AS avg_cpu_ms,
    rs.avg_logical_io_reads            AS avg_logical_reads,
    rs.avg_physical_io_reads           AS avg_physical_reads,
    rs.avg_rowcount,
    rs.avg_memory_grant_kb
FROM sys.query_store_runtime_stats      rs
JOIN sys.query_store_runtime_stats_interval rsi
    ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
JOIN sys.query_store_plan               p
    ON rs.plan_id = p.plan_id
JOIN sys.query_store_query              q
    ON p.query_id = q.query_id
JOIN sys.query_store_query_text         qt
    ON q.query_text_id = qt.query_text_id
WHERE rsi.start_time >= @start_time
  AND rsi.end_time   <= @end_time
  AND rs.execution_type = 0            -- Regular execution のみ
ORDER BY rs.total_duration DESC;

-- プランのリグレッション検知：昨日と今日で同一クエリの性能を比較
WITH today AS (
    SELECT
        q.query_id,
        SUM(rs.count_executions)  AS exec_today,
        AVG(rs.avg_duration)      AS avg_dur_today
    FROM sys.query_store_runtime_stats rs
    JOIN sys.query_store_runtime_stats_interval rsi
        ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
    JOIN sys.query_store_plan p ON rs.plan_id = p.plan_id
    JOIN sys.query_store_query q ON p.query_id = q.query_id
    WHERE rsi.start_time >= DATEADD(HOUR, -24, GETUTCDATE())
    GROUP BY q.query_id
),
yesterday AS (
    SELECT
        q.query_id,
        SUM(rs.count_executions)  AS exec_yesterday,
        AVG(rs.avg_duration)      AS avg_dur_yesterday
    FROM sys.query_store_runtime_stats rs
    JOIN sys.query_store_runtime_stats_interval rsi
        ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
    JOIN sys.query_store_plan p ON rs.plan_id = p.plan_id
    JOIN sys.query_store_query q ON p.query_id = q.query_id
    WHERE rsi.start_time >= DATEADD(HOUR, -48, GETUTCDATE())
      AND rsi.start_time  < DATEADD(HOUR, -24, GETUTCDATE())
    GROUP BY q.query_id
)
SELECT
    t.query_id,
    qt.query_sql_text,
    y.avg_dur_yesterday / 1000.0   AS avg_dur_yesterday_ms,
    t.avg_dur_today     / 1000.0   AS avg_dur_today_ms,
    CAST(
        (t.avg_dur_today - y.avg_dur_yesterday) * 100.0
        / NULLIF(y.avg_dur_yesterday, 0) AS DECIMAL(6,1)
    )                              AS pct_change
FROM today t
JOIN yesterday y ON t.query_id = y.query_id
JOIN sys.query_store_query q ON t.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE t.avg_dur_today > y.avg_dur_yesterday * 1.5   -- 50% 以上悪化したもの
ORDER BY pct_change DESC;

-- クエリごとの待機カテゴリ内訳
SELECT TOP 20
    qt.query_sql_text,
    ws.wait_category_desc,
    SUM(ws.total_query_wait_time_ms)   AS total_wait_ms,
    AVG(ws.avg_query_wait_time_ms)     AS avg_wait_ms
FROM sys.query_store_wait_stats ws
JOIN sys.query_store_plan   p  ON ws.plan_id  = p.plan_id
JOIN sys.query_store_query  q  ON p.query_id  = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_runtime_stats_interval rsi
    ON ws.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(HOUR, -24, GETUTCDATE())
GROUP BY qt.query_sql_text, ws.wait_category_desc
ORDER BY total_wait_ms DESC;

-- プランを固定する（plan_id は query_store_plan ビューで確認）
EXEC sys.sp_query_store_force_plan
    @query_id = 42,
    @plan_id  = 101;

-- 固定を解除する
EXEC sys.sp_query_store_unforce_plan
    @query_id = 42,
    @plan_id  = 101;

-- 現在強制されているプランの確認
SELECT
    q.query_id, qt.query_sql_text,
    p.plan_id, p.is_forced_plan,
    p.force_failure_count,
    p.last_force_failure_reason_desc
FROM sys.query_store_plan p
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE p.is_forced_plan = 1;