【SQL Server】Query Storeの使い方｜遅いSQL・実行計画の変更・性能劣化を調査

SELECT
    actual_state_desc,
    desired_state_desc,
    readonly_reason,
    current_storage_size_mb,
    max_storage_size_mb,
    query_capture_mode_desc,
    size_based_cleanup_mode_desc,
    wait_stats_capture_mode_desc
FROM sys.database_query_store_options;

ALTER DATABASE YourDatabaseName
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_STORAGE_SIZE_MB = 1024,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    SIZE_BASED_CLEANUP_MODE = AUTO,
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 15,
    MAX_PLANS_PER_QUERY = 200,
    WAIT_STATS_CAPTURE_MODE = ON
);

SELECT TOP (30)
    qsq.query_id,
    qsp.plan_id,
    SUM(rs.count_executions) AS executions,
    AVG(rs.avg_duration) / 1000.0 AS avg_duration_ms,
    AVG(rs.avg_cpu_time) / 1000.0 AS avg_cpu_ms,
    AVG(rs.avg_logical_io_reads) AS avg_logical_reads,
    MAX(rs.last_execution_time) AS last_execution_time,
    LEFT(qt.query_sql_text, 4000) AS query_text
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS qsq
    ON qsq.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS qsp
    ON qsp.query_id = qsq.query_id
JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = qsp.plan_id
GROUP BY qsq.query_id, qsp.plan_id, qt.query_sql_text
ORDER BY avg_duration_ms DESC;

WITH stats AS (
    SELECT
        qsq.query_id,
        qsp.plan_id,
        qt.query_sql_text,
        rsi.start_time,
        rs.count_executions,
        rs.avg_duration,
        rs.avg_cpu_time,
        rs.avg_logical_io_reads
    FROM sys.query_store_query_text AS qt
    JOIN sys.query_store_query AS qsq
        ON qsq.query_text_id = qt.query_text_id
    JOIN sys.query_store_plan AS qsp
        ON qsp.query_id = qsq.query_id
    JOIN sys.query_store_runtime_stats AS rs
        ON rs.plan_id = qsp.plan_id
    JOIN sys.query_store_runtime_stats_interval AS rsi
        ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
    WHERE rsi.start_time >= DATEADD(hour, -2, SYSDATETIMEOFFSET())
)
SELECT TOP (30)
    query_id,
    plan_id,
    SUM(CASE WHEN start_time >= DATEADD(hour, -1, SYSDATETIMEOFFSET()) THEN count_executions ELSE 0 END) AS recent_execs,
    AVG(CASE WHEN start_time >= DATEADD(hour, -1, SYSDATETIMEOFFSET()) THEN avg_duration END) / 1000.0 AS recent_avg_ms,
    AVG(CASE WHEN start_time <  DATEADD(hour, -1, SYSDATETIMEOFFSET()) THEN avg_duration END) / 1000.0 AS previous_avg_ms,
    LEFT(query_sql_text, 1000) AS query_text
FROM stats
GROUP BY query_id, plan_id, query_sql_text
HAVING AVG(CASE WHEN start_time >= DATEADD(hour, -1, SYSDATETIMEOFFSET()) THEN avg_duration END)
     > AVG(CASE WHEN start_time <  DATEADD(hour, -1, SYSDATETIMEOFFSET()) THEN avg_duration END) * 2
ORDER BY recent_avg_ms DESC;

SELECT TOP (50)
    qsq.query_id,
    COUNT(DISTINCT qsp.plan_id) AS plan_count,
    SUM(rs.count_executions) AS executions,
    AVG(rs.avg_duration) / 1000.0 AS avg_duration_ms,
    LEFT(qt.query_sql_text, 1000) AS query_text
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS qsq
    ON qsq.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS qsp
    ON qsp.query_id = qsq.query_id
JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = qsp.plan_id
GROUP BY qsq.query_id, qt.query_sql_text
HAVING COUNT(DISTINCT qsp.plan_id) > 1
ORDER BY plan_count DESC, avg_duration_ms DESC;

DECLARE @query_id bigint = 123; -- 対象query_idに置き換える

SELECT
    qsp.plan_id,
    qsp.is_forced_plan,
    qsp.force_failure_count,
    qsp.last_force_failure_reason_desc,
    SUM(rs.count_executions) AS executions,
    AVG(rs.avg_duration) / 1000.0 AS avg_duration_ms,
    AVG(rs.avg_cpu_time) / 1000.0 AS avg_cpu_ms,
    AVG(rs.avg_logical_io_reads) AS avg_logical_reads,
    MAX(rs.last_execution_time) AS last_execution_time,
    qsp.query_plan
FROM sys.query_store_plan AS qsp
JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = qsp.plan_id
WHERE qsp.query_id = @query_id
GROUP BY qsp.plan_id, qsp.is_forced_plan, qsp.force_failure_count,
         qsp.last_force_failure_reason_desc, qsp.query_plan
ORDER BY avg_duration_ms DESC;

SELECT TOP (30)
    qsq.query_id,
    qsp.plan_id,
    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,
    LEFT(qt.query_sql_text, 1000) AS query_text
FROM sys.query_store_wait_stats AS ws
JOIN sys.query_store_plan AS qsp
    ON qsp.plan_id = ws.plan_id
JOIN sys.query_store_query AS qsq
    ON qsq.query_id = qsp.query_id
JOIN sys.query_store_query_text AS qt
    ON qt.query_text_id = qsq.query_text_id
GROUP BY qsq.query_id, qsp.plan_id, ws.wait_category_desc, qt.query_sql_text
ORDER BY total_wait_ms DESC;

-- query_idとplan_idは実環境で確認した値を使う
EXEC sys.sp_query_store_force_plan
    @query_id = 123,
    @plan_id = 456;

-- 解除する場合
EXEC sys.sp_query_store_unforce_plan
    @query_id = 123,
    @plan_id = 456;

-- query_idはQuery Storeで確認した値を使う
EXEC sys.sp_query_store_set_hints
    @query_id = 123,
    @value = N'OPTION (OPTIMIZE FOR UNKNOWN)';

-- ヒントを解除する場合
EXEC sys.sp_query_store_clear_hints
    @query_id = 123;

SELECT
    query_hint_id,
    query_id,
    query_hint_text,
    last_query_hint_failure_reason_desc,
    query_hint_failure_count,
    source_desc
FROM sys.query_store_query_hints
ORDER BY query_id;

SELECT
    actual_state_desc,
    readonly_reason,
    current_storage_size_mb,
    max_storage_size_mb,
    stale_query_threshold_days,
    size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

-- 容量上限を増やす例
ALTER DATABASE YourDatabaseName
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 2048);

-- 古いデータの保持期間を調整する例
ALTER DATABASE YourDatabaseName
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30));