クエリストアを使用してパフォーマンスを調整する

[アーティクル]
11/19/2024

適用対象: SQL Server 2016 (13.x) 以降のバージョンAzure SQL Database Azure SQL Managed Instance Azure Synapse Analytics (専用 SQL プールのみ))、Microsoft Fabric の SQL データベース

SQL Server クエリストア機能を使用すると、SQL Server Management Studio ビジュアルインターフェイスと Transact-SQL クエリを通じて、ワークロードでクエリを検出およびチューニングできます。この記事では、使用統計情報に基づいてクエリを識別する方法やプランを強制する方法など、データベースのクエリパフォーマンスを向上させるために実行可能な情報を取得する方法について詳しく説明します。また、クエリストアのヒント機能を使用して、アプリケーションコードを変更せずにクエリを特定し、クエリプランを作成することもできます。

このデータの収集方法の詳細については、「クエリストアでデータを収集する方法」を参照してください。
クエリストアを使用した構成と管理の詳細については、「クエリストアを使用したパフォーマンスの監視」を参照してください。
Azure SQL Database におけるクエリストアの運用の詳細については、「 Azure SQL Database でクエリストアを運用する」を参照してください。

パフォーマンスチューニングのサンプルクエリ

クエリのストアには、コンパイルの履歴とクエリの実行全体に関するランタイムメトリックスが保持されており、ワークロードに関連した質問の答えを見つけることができます。

次のサンプルクエリは、パフォーマンスベースラインとクエリパフォーマンスの調査に役立つ場合があります。

データベースで最近実行されたクエリ

データベースで過去 1 時間以内に最近実行された n 個のクエリ:

SELECT TOP 10 qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    p.plan_id,
    rs.last_execution_time
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
ORDER BY rs.last_execution_time DESC;

実行回数

過去 1 時間以内の各クエリの実行回数:

SELECT q.query_id,
    qt.query_text_id,
    qt.query_sql_text,
    SUM(rs.count_executions) AS total_execution_count
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY q.query_id,
    qt.query_text_id,
    qt.query_sql_text
ORDER BY total_execution_count DESC;

最長平均実行時間

過去 1 時間以内に平均期間が最も長いクエリの数:

SELECT TOP 10 ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) /
        NULLIF(SUM(rs.count_executions), 0), 2) avg_duration,
    SUM(rs.count_executions) AS total_execution_count,
    qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    p.plan_id,
    GETUTCDATE() AS CurrentUTCTime,
    MAX(rs.last_execution_time) AS last_execution_time
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    p.plan_id
ORDER BY avg_duration DESC;

最大平均物理 I/O 読み取り数

過去 24 時間で平均物理 I/O 読み取り数が多かったクエリの上位と、対応する平均行数および実行カウント。

SELECT TOP 10 rs.avg_physical_io_reads,
    qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    p.plan_id,
    rs.runtime_stats_id,
    rsi.start_time,
    rsi.end_time,
    rs.avg_rowcount,
    rs.count_executions
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
INNER 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, -24, GETUTCDATE())
ORDER BY rs.avg_physical_io_reads DESC;

複数のプランを持つクエリ

複数のプランを含むクエリは、プランの選択の変更によるパフォーマンス低下の候補になる可能性があるため、特に興味深いものです。

次のクエリは、過去 1 時間以内にプランの数が最も多いクエリを識別します。

SELECT q.query_id,
    object_name(object_id) AS ContainingObject,
    COUNT(*) AS QueryPlanCount,
    STRING_AGG(p.plan_id, ',') plan_ids,
    qt.query_sql_text
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY OBJECT_NAME(object_id),
    q.query_id,
    qt.query_sql_text
HAVING COUNT(DISTINCT p.plan_id) > 1
ORDER BY QueryPlanCount DESC;

次のクエリは、これらのクエリと過去 1 時間以内のすべてのプランを識別します。

WITH Query_MultPlans
AS (
    SELECT COUNT(*) AS QueryPlanCount,
        q.query_id
    FROM sys.query_store_query_text AS qt
    INNER JOIN sys.query_store_query AS q
        ON qt.query_text_id = q.query_text_id
    INNER JOIN sys.query_store_plan AS p
        ON p.query_id = q.query_id
    GROUP BY q.query_id
    HAVING COUNT(DISTINCT plan_id) > 1
)
SELECT q.query_id,
    object_name(object_id) AS ContainingObject,
    query_sql_text,
    p.plan_id,
    p.query_plan AS plan_xml,
    p.last_compile_start_time,
    p.last_execution_time
FROM Query_MultPlans AS qm
INNER JOIN sys.query_store_query AS q
    ON qm.query_id = q.query_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_query_text qt
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
ORDER BY q.query_id,
    p.plan_id;

最長待機時間

このクエリでは、過去 1 時間で待機時間が最も長い上位 10 件のクエリを返します。

SELECT TOP 10 qt.query_text_id,
    q.query_id,
    p.plan_id,
    sum(total_query_wait_time_ms) AS sum_total_wait_ms
FROM sys.query_store_wait_stats ws
INNER JOIN sys.query_store_plan p
    ON ws.plan_id = p.plan_id
INNER JOIN sys.query_store_query q
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_query_text qt
    ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY qt.query_text_id,
    q.query_id,
    p.plan_id
ORDER BY sum_total_wait_ms DESC;

Note

Azure Synapse Analytics では、Azure Synapse Analytics クエリストア DMV では利用できない待機統計を除き、このセクションのクエリストアのサンプルクエリをサポートしています。

最近パフォーマンスが低下したクエリ

次のクエリの例では、プラン選択の変更により、過去 48 時間で実行時間が 2 倍になったすべてのクエリを返します。このクエリでは、すべてのランタイム統計情報の時間間隔を並べて比較します。

SELECT qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    rs1.runtime_stats_id AS runtime_stats_id_1,
    rsi1.start_time AS interval_1,
    p1.plan_id AS plan_1,
    rs1.avg_duration AS avg_duration_1,
    rs2.avg_duration AS avg_duration_2,
    p2.plan_id AS plan_2,
    rsi2.start_time AS interval_2,
    rs2.runtime_stats_id AS runtime_stats_id_2
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p1
    ON q.query_id = p1.query_id
INNER JOIN sys.query_store_runtime_stats AS rs1
    ON p1.plan_id = rs1.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi1
    ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id
INNER JOIN sys.query_store_plan AS p2
    ON q.query_id = p2.query_id
INNER JOIN sys.query_store_runtime_stats AS rs2
    ON p2.plan_id = rs2.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi2
    ON rsi2.runtime_stats_interval_id = rs2.runtime_stats_interval_id
WHERE rsi1.start_time > DATEADD(hour, -48, GETUTCDATE())
    AND rsi2.start_time > rsi1.start_time
    AND p1.plan_id <> p2.plan_id
    AND rs2.avg_duration > 2 * rs1.avg_duration
ORDER BY q.query_id,
    rsi1.start_time,
    rsi2.start_time;

(プラン選択の変更に関連するものだけでなく) パフォーマンス低下に関するすべての情報を表示するには、前のクエリから条件 AND p1.plan_id <> p2.plan_id を削除します。

パフォーマンス低下の履歴を使用したクエリ

最近の実行と過去の実行を比較する必要がある場合は、次のクエリで、実行期間に基づいてクエリの実行を比較します。この例では、クエリは、最近の期間 (1 時間) と履歴の期間 (過去 1 日間) とで実行を比較し、additional_duration_workload の原因となったものを識別します。このメトリックは、最近の平均実行と履歴の平均実行に最近実行の数を掛けた値の間の差として計算されます。これは、履歴と比較して、これらの最近の実行によって必要となった余分な期間を表します。

--- "Recent" workload - last 1 hour
DECLARE @recent_start_time DATETIMEOFFSET;
DECLARE @recent_end_time DATETIMEOFFSET;

SET @recent_start_time = DATEADD(hour, - 1, SYSUTCDATETIME());
SET @recent_end_time = SYSUTCDATETIME();

--- "History" workload
DECLARE @history_start_time DATETIMEOFFSET;
DECLARE @history_end_time DATETIMEOFFSET;

SET @history_start_time = DATEADD(hour, - 24, SYSUTCDATETIME());
SET @history_end_time = SYSUTCDATETIME();

WITH hist AS (
    SELECT p.query_id query_id,
        ROUND(ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) * 0.001, 2), 2) AS total_duration,
        SUM(rs.count_executions) AS count_executions,
        COUNT(DISTINCT p.plan_id) AS num_plans
    FROM sys.query_store_runtime_stats AS rs
    INNER JOIN sys.query_store_plan AS p
        ON p.plan_id = rs.plan_id
    WHERE (
        rs.first_execution_time >= @history_start_time
        AND rs.last_execution_time < @history_end_time
    )
    OR (
        rs.first_execution_time <= @history_start_time
        AND rs.last_execution_time > @history_start_time
    )
    OR (
        rs.first_execution_time <= @history_end_time
        AND rs.last_execution_time > @history_end_time
    )
    GROUP BY p.query_id
),
recent AS (
    SELECT p.query_id query_id,
        ROUND(ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) * 0.001, 2), 2) AS total_duration,
        SUM(rs.count_executions) AS count_executions,
        COUNT(DISTINCT p.plan_id) AS num_plans
    FROM sys.query_store_runtime_stats AS rs
    INNER JOIN sys.query_store_plan AS p
        ON p.plan_id = rs.plan_id
    WHERE (
        rs.first_execution_time >= @recent_start_time
        AND rs.last_execution_time < @recent_end_time
    )
    OR (
        rs.first_execution_time <= @recent_start_time
        AND rs.last_execution_time > @recent_start_time
    )
    OR (
        rs.first_execution_time <= @recent_end_time
        AND rs.last_execution_time > @recent_end_time
    )
    GROUP BY p.query_id
    )
SELECT results.query_id AS query_id,
    results.query_text AS query_text,
    results.additional_duration_workload AS additional_duration_workload,
    results.total_duration_recent AS total_duration_recent,
    results.total_duration_hist AS total_duration_hist,
    ISNULL(results.count_executions_recent, 0) AS count_executions_recent,
    ISNULL(results.count_executions_hist, 0) AS count_executions_hist
FROM (
    SELECT hist.query_id AS query_id,
        qt.query_sql_text AS query_text,
        ROUND(CONVERT(FLOAT, recent.total_duration / recent.count_executions - hist.total_duration / hist.count_executions) * (recent.count_executions), 2) AS additional_duration_workload,
        ROUND(recent.total_duration, 2) AS total_duration_recent,
        ROUND(hist.total_duration, 2) AS total_duration_hist,
        recent.count_executions AS count_executions_recent,
        hist.count_executions AS count_executions_hist
    FROM hist
    INNER JOIN recent
        ON hist.query_id = recent.query_id
    INNER JOIN sys.query_store_query AS q
        ON q.query_id = hist.query_id
    INNER JOIN sys.query_store_query_text AS qt
        ON q.query_text_id = qt.query_text_id
) AS results
WHERE additional_duration_workload > 0
ORDER BY additional_duration_workload DESC
OPTION (MERGE JOIN);

クエリパフォーマンスの安定性を維持する

複数回実行されるクエリでは、SQL Server で異なるプランが使用された結果、リソース使用率や期間が異なっていることに気付く場合があります。クエリストアを使用すると、クエリパフォーマンスが低下している時点を検出し、対象期間の最適なプランを特定できます。こうすることで、将来のクエリの実行で最適なプランを強制的に適用できます。

パラメーターを持つクエリ (自動的にパラメーター化されたもの、または手動でパラメーター化されたもののいずれか) に関して、クエリパフォーマンスが一定ではないものを特定することもできます。さまざまなプランの中で、すべてまたはほとんどのパラメーター値に対して高速かつ最適なプランを特定し、そのプランを強制的に適用できます。これにより、より広範なユーザーシナリオで予測可能なパフォーマンスが維持されます。

クエリに対してプランを強制する (強制ポリシーの適用)

特定のクエリに対してプランを強制すると、SQL Server 層のオプティマイザーでプランの強制が試みられます。プランの適用に失敗した場合、拡張イベントが発生し、オプティマイザーに対して通常の方法で最適化するように指示されます。

EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49;

sp_query_store_force_plan を使用する場合は、クエリのストアによって対象のクエリのプランとして記録されたプランのみを強制できます。つまり、クエリで使用できるプランは、クエリストアがアクティブであったときにクエリを実行するために既に使用されているプランのみです。

Note

クエリストアでのプランの強制は、Azure Synapse Analytics ではサポートされていません。

高速順方向カーソルと静的カーソルのサポートを強制するプラン

SQL Server 2019 (15.x) 以降のバージョンおよび Azure SQL Database (すべてのデプロイモデル) では、クエリストアにおいて、高速順方向カーソルおよび Transact-SQL と API の静的カーソルに対してクエリ実行プランを強制する機能がサポートされます。強制は、sp_query_store_force_plan または SQL Server Management Studio のクエリストアレポートによってサポートされます。

クエリに対するプランの強制を解除する

もう一度 SQL Server クエリオプティマイザーを利用して最適なクエリプランを計算するには、クエリに対して選択されていたプランの強制を sp_query_store_unforce_plan を使用して解除します。

EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49;

次の方法で共有

クエリストアを使用してパフォーマンスを調整する

パフォーマンスチューニングのサンプルクエリ

データベースで最近実行されたクエリ

実行回数

最長平均実行時間

最大平均物理 I/O 読み取り数

複数のプランを持つクエリ

最長待機時間

最近パフォーマンスが低下したクエリ

パフォーマンス低下の履歴を使用したクエリ

クエリパフォーマンスの安定性を維持する

クエリに対してプランを強制する (強制ポリシーの適用)

高速順方向カーソルと静的カーソルのサポートを強制するプラン

クエリに対するプランの強制を解除する

フィードバック

その他のリソース

次の方法で共有

クエリ ストアを使用してパフォーマンスを調整する

パフォーマンス チューニングのサンプル クエリ

データベースで最近実行されたクエリ

実行回数

最長平均実行時間

最大平均物理 I/O 読み取り数

複数のプランを持つクエリ

最長待機時間

最近パフォーマンスが低下したクエリ

パフォーマンス低下の履歴を使用したクエリ

クエリ パフォーマンスの安定性を維持する

クエリに対してプランを強制する (強制ポリシーの適用)

高速順方向カーソルと静的カーソルのサポートを強制するプラン

クエリに対するプランの強制を解除する

関連するコンテンツ

フィードバック

その他のリソース

クエリストアを使用してパフォーマンスを調整する

パフォーマンスチューニングのサンプルクエリ

クエリパフォーマンスの安定性を維持する