クエリ ストアを管理するためのベスト プラクティス

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance

この記事では、SQL Server クエリ ストアとその周辺機能の管理について説明します。

• クエリ ストアを使用した構成と管理の詳細については、「クエリ ストアを使用したパフォーマンスの監視」を参照してください。

Azure SQL Database のクエリ ストアの既定値

このセクションでは、クエリ ストアおよび依存機能を確実に操作できるように設計された Azure SQL Database での最適な構成の既定値について説明します。 既定の構成は、データ収集が継続的に実施される (OFF/READ_ONLY 状態の時間が最小限になる) ように最適化されています。 使用可能なすべてのクエリ ストア オプションの詳細については、「ALTER DATABASE の SET オプション (Transact-SQL)」を参照してください。

カスタム設定を維持する場合は、 ALTER DATABASE とクエリ ストア オプション を使用して、構成を前の状態に戻します。 「クエリ ストアを使用する際の推奨事項」で、最適構成のパラメーターを選ぶ方法確認してください。

最適なクエリ ストア キャプチャ モードを設定する

最も重要なデータをクエリ ストアに保存します。 次の表では、各クエリ ストア キャプチャ モードの一般的なシナリオについて説明します。

最も重要なデータをクエリ ストアに保存する

関連データのみを含むようにクエリ ストアを構成して、継続的に実行されるようにし、通常のワークロードへの影響を最小限に抑えながら、優れたトラブルシューティング エクスペリエンスを提供します。

次の表に、推奨事項を示します。

カスタム キャプチャ ポリシー

CUSTOMクエリストアキャプチャ モードが有効にすると、新しいクエリ ストア キャプチャ ポリシーの設定で追加のクエリ ストア構成を使用して、特定のサーバーでのデータ収集を微チューニングすることができます。

新しいカスタム設定では、内部キャプチャ ポリシーの時間のしきい値内で何が行われるかが定義されます。 これは、構成可能な条件が評価される時刻の境界であり、いずれかが true の場合、クエリがクエリ ストアによるキャプチャの対象となります。

クエリ ストアのキャプチャ モード: クエリ ストアのクエリ キャプチャ ポリシーを指定します。

• All：すべてのクエリをキャプチャします。 SQL Server 2016 (13.x) と SQL Server 2017 (14.x) では、このオプションは既定値です。
• Auto：頻度の低いクエリと、コンパイルと実行時間の短いクエリは無視されます。 実行回数、コンパイル、実行時間のしきい値は内部的に決定されます。 SQL Server 2019 (15.x) 以降では、これは既定値のオプションです。
• None：クエリ ストアが新しいクエリのキャプチャを停止します。
• カスタム:追加の制御と機能を使用して、データ収集ポリシーを微チューニングできます。 新しいカスタム設定では、内部キャプチャ ポリシーの時間のしきい値内で何が行われるかが定義されます。 これは、構成可能な条件が評価される時刻の境界であり、いずれかが true の場合、クエリがクエリ ストアによるキャプチャの対象となります。

環境に適したカスタム キャプチャ ポリシーのチューニングは、次の場合に考慮する必要があります。

• データベースが非常に大きい。
• データベースには、多数の一意のアドホック クエリがあります。
• データベースには、特定のサイズまたは拡張の制限があります。

カスタム キャプチャ ポリシーの例

次の例では、QUERY_CAPTURE_MODEを AUTO に設定し、カスタム キャプチャ モードを設定します。 次の各設定手順では、カスタム キャプチャ ポリシーを SQL Server 2022 (16.x) の既定値に設定します。 これらの値をチューニングして、キャプチャされるクエリの数を減らし、クエリ ストアのディスク上の占有領域を減らすことを検討してください。 これらの値は少しずつ変更することをおすすめします。

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

次のサンプル クエリでは、EXECUTION_COUNT と TOTAL_COMPILE_CPU_TIME_MS の既定の設定をオーバーライドするカスタム キャプチャ ポリシーを使用するように既存のクエリ ストアを変更します。

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        EXECUTION_COUNT = 100,
        TOTAL_COMPILE_CPU_TIME_MS = 10000
      )
    );

クエリ ストアの最大サイズ

SQL Server 2019 (15.x) 以降、クエリ ストアの既定の最大サイズ値は 1000 MB です。 以前のバージョンでは、既定値は 100 MB でした。 一意のクエリ プランが多数あるビジー状態のデータベースでは、クエリ ストアの最大サイズの制限を大きくすることが適切です。 キャプチャ ポリシーのチューニング (前のセクションを参照) は、クエリ ストアのディスク上のサイズを制限し、クエリ ストアがREAD_ONLY モードに入らないようにするためのより重要な考慮事項です。 クエリ ストアでクエリ、実行プラン、および統計情報が収集されている間は、この制限に達するまでデータベース内のサイズが増え続けます。 サイズが制限に達すると、クエリ ストアの操作モードが自動的にREAD_ONLYに切り替わり、新しいデータの収集が停止します。以降、パフォーマンス分析は正確ではなくなります。

• SQL Server と Azure SQL Managed Instance では、MAX_STORAGE_SIZE_MB 制限は厳密には適用されません。
• Azure SQL データベースでは、MAX_STORAGE_SIZE_MB の最大許容値は 10,240 MB (メガバイト)です。

ストレージ サイズは、クエリ ストアでディスクにデータが書き込まれる場合にのみ確認されます。 この間隔は DATA_FLUSH_INTERVAL_SECONDS オプションか、Management Studio クエリ ストアのダイアログ オプションである [データのフラッシュ間隔] によって設定されます。

• 間隔の既定値は 900 秒 (15 分) です。
• クエリ ストアでストレージ サイズの確認の合間に MAX_STORAGE_SIZE_MB の制限を超えた場合は、読み取り専用モードに移行します。
• SIZE_BASED_CLEANUP_MODE が有効になっている場合は、MAX_STORAGE_SIZE_MB の制限を適用するクリーンアップ メカニズムもトリガーされます。
  • 十分な領域が消去されると、クエリ ストア モードが自動的に READ_WRITE に戻ります。

詳細については、ALTER DATABASE SET OPTION MAX_STORAGE_SIZE_MB を参照してください。

データ フラッシュ間隔 (分)

データ フラッシュ間隔は、収集されたランタイム統計がディスクに保持されるまでの頻度を定義します。 SQL Server Management Studio では、値は分単位ですが、Transact-SQL では秒単位で表されます。 既定値は 15 分 (900 秒) です。

• データ フラッシュ間隔を長くすると、クエリ ストアストレージの I/O への全体的な影響が減少する可能性がありますが、ストレージ I/O ワークロードはスパイク性が高くなり、ディスク使用率への影響は少なくなりますが、影響が大きくなります。 ワークロードで生成される異なるクエリとプランの数が多くない場合、またはデータベースをシャットダウンする前にデータを長時間保持できる場合は、大きい値を使用することを検討してください。
• データ フラッシュ間隔を短くすると、シャットダウン、電源損失、またはフェールオーバーが発生した場合に失われるクエリ ストア データの量が減少します。 また、より頻繁にディスクに書き込むことで、クエリ ストアからのストレージ I/O への影響がスムーズになる可能性がありますが、データは少なくなります。

クエリ ストアの既定値を変更する

ワークロードとパフォーマンスのトラブルシューティングの要件に基づいて、クエリ ストアを構成します。 始めは既定のパラメーターで十分ですが、時間の経過と共にクエリ ストアがどのように動作するかを監視し、それに応じて構成を調整する必要があります。

クエリ ストアの現在の設定を表示する

SQL Server Management Studio (SSMS) または T-SQL で現在のクエリ ストア設定を表示します。

USE [QueryStoreDB];
GO

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

次のスクリプトでは、[最大サイズ (MB)] の新しい値を設定します。

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);

[データ フラッシュ間隔] に別の値を設定するには、SQL Server Management Studio または Transact-SQL を使用します。

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 900);

統計情報の収集間隔: 実行時統計情報を収集する間隔を定義します (分単位で表示)。 既定値は 60 分です。 より細かい粒度が必要な場合、または問題を検出して軽減するための時間を短くする場合は、小さい値を使用することを検討してください。 値はクエリ ストア データのサイズに直接影響することに注意してください。 [統計情報の収集間隔] に別の値を設定するには、SQL Server Management Studio または Transact-SQL を使用します。

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60);

古いクエリのしきい値 (日): 保存する実行時統計と、日にちで表示される非アクティブ クエリの保有期間を制御する、時間ベースのクリーンアップ ポリシーです。 既定では、クエリ ストアはデータを 30 日間保持するよう構成されていますが、シナリオによっては必要以上に長い場合があります。

使用予定のない履歴データは保持しないようにしてください。 これにより、読み取り専用状態への移行を減らすことができます。 クエリ ストアのデータのサイズと、問題を検出して軽減するまでの時間を予測しやすくなります。 時間ベースのクリーンアップ ポリシーを構成するには、 Management Studio または次のスクリプトを使用します。

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90));

サイズ ベースのクリーンアップ モード: クエリ ストアのデータ サイズが制限に達したときに、データの自動クリーンアップを行うかどうかを指定します。 クエリ ストアが常に読み取り/書き込みモードで実行され、最新データが収集されるようにするには、サイズ ベースのクリーンアップを有効にします。 負荷の高いワークロード下では、クエリ ストアのクリーンアップによってデータ サイズが常にその制限以下に維持されるという保証はありません。 自動データ クリーンアップが遅延し、読み取り専用モードに (一時的に) 切り替わる可能性があります。

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);

クエリ ストアのキャプチャ モード: クエリ ストアのクエリ キャプチャ ポリシーを指定します。

• All：すべてのクエリをキャプチャします。 SQL Server 2016 (13.x) と SQL Server 2017 (14.x) では、このオプションは既定値です。
• Auto：頻度の低いクエリと、コンパイルと実行時間の短いクエリは無視されます。 実行回数、コンパイル、実行時間のしきい値は内部的に決定されます。 SQL Server 2019 (15.x) 以降では、これは既定値のオプションです。
• None：クエリ ストアが新しいクエリのキャプチャを停止します。
• カスタム:追加の制御と機能を使用して、データ収集ポリシーを微チューニングできます。 新しいカスタム設定では、内部キャプチャ ポリシーの時間のしきい値内で何が行われるかが定義されます。 これは、構成可能な条件が評価される時刻の境界であり、いずれかが true の場合、クエリがクエリ ストアによるキャプチャの対象となります。

次のスクリプトでは、QUERY_CAPTURE_MODE を AUTO に設定します。

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);

例

次の例では、QUERY_CAPTURE_MODE を AUTO に設定し、SQL Server 2016 (13.x) のその他の推奨オプションを設定します。

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60
    );

次の例では、QUERY_CAPTURE_MODE を AUTO に設定し、待機統計を含めるように SQL Server 2017 (14.x) のその他の推奨オプションを設定します。

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON
    );

次の例では、新しい既定の AUTO キャプチャ モードではなく、CUSTOM キャプチャ ポリシーを SQL Server 2019 (15.x) の既定値に設定します。 カスタム キャプチャ ポリシーのオプションと既定値の詳細については、 「<query_capture_policy_option_list>」を参照してください。

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

クエリ ストアのメンテナンス

このセクションでは、クエリのストアの機能自体を管理する方法に関するガイドラインを示します。

クエリ ストアの状態

クエリ ストアではユーザー データベース内にデータが格納されるため、サイズに上限が設定されています (MAX_STORAGE_SIZE_MB で構成)。 クエリのストア内のデータがその上限に達すると、クエリのストアは自動的に状態を読み取り/書き込みから読み取り専用に変更し、新しいデータの収集を停止します。

sys.database_query_store_options のクエリを実行して、クエリのストアが現在アクティブであるか、また、ランタイム統計情報を現在収集しているかを確認します。

SELECT actual_state, actual_state_desc, readonly_reason,
    current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

クエリ ストアの状態は、actual_state 列によって決まります。 目的の状態と異なる場合は、readonly_reason 列で詳しい情報が得られます。 クエリ ストアのサイズがクォータを超える場合、この機能は read_only モードに切り替わり、理由が表示されます。 理由の詳細については、「sys.database_query_store_options 」を参照してください。

クエリのストアのオプションを取得する

クエリのストアの状態に関する詳細情報については、ユーザー データベースで次を実行します。

SELECT * FROM sys.database_query_store_options;

クエリ ストア間隔を設定する

クエリのランタイム統計情報を集計する時間間隔 (既定では 60 分) をオーバーライドできます。 時間間隔に使用する新しい値は、sys.database_query_store_options ビューで公開されます。

ALTER DATABASE <database_name>
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);

INTERVAL_LENGTH_MINUTES に任意の値を使用することはできません。 1、5、10、15、30、60、または 1440 分のいずれかのサイクル間隔を使用します。

クエリのストアの使用領域

現在のクエリのストアのサイズと制限をチェックするには、ユーザー データベースで次のステートメントを実行します。

SELECT current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

クエリのストアの記憶域がいっぱいの場合は、次のステートメントを使用して記憶域を拡張します。

ALTER DATABASE <database_name>
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <new_size>);

クエリ ストアオプションの設定

単一の ALTER DATABASE ステートメントで、クエリのストアの複数のオプションを一度にまとめて設定できます。

ALTER DATABASE <database name>
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 3000,
    MAX_STORAGE_SIZE_MB = 500,
    INTERVAL_LENGTH_MINUTES = 15,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 1000,
    WAIT_STATS_CAPTURE_MODE = ON
);

構成オプションの完全一覧は、「ALTER DATABASE の SET オプション (Transact-SQL)」を参照してください。

領域をクリーンアップする

クエリのストアの内部テーブルは、データベースの作成時に PRIMARY ファイル グループに作成され、その構成を後で変更することはできません。 領域が不足している場合は、次のステートメントを使用して、古いクエリ ストアのデータを消去できます。

ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;

または、アドホック クエリ データはクエリの最適化やプラン分析との関連性が低く、ただ場所を占有するだけなので、アドホック クエリ データのみを削除することもできます。

Azure Synapse Analytics では、クエリ ストアの消去はできません。 過去 7 日間のデータが自動的に保持されます。

アドホック クエリの削除

これにより、アドホック クエリと内部クエリがクエリ ストアから削除されるため、クエリ ストアの領域が不足することはなく、本当に追跡する必要があるクエリが削除されることもありません。

SET NOCOUNT ON
-- This purges adhoc and internal queries from
-- the Query Store in the current database
-- so that the Query Store does not run out of space
-- and remove queries we really need to track

DECLARE @id int;
DECLARE adhoc_queries_cursor CURSOR
FOR
    SELECT q.query_id
    FROM sys.query_store_query_text AS qt
    JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
    JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
    JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
    WHERE q.is_internal_query = 1  -- is it an internal query then we dont care to keep track of it
       OR q.object_id = 0 -- if it does not have a valid object_id then it is an adhoc query and we don't care about keeping track of it
    GROUP BY q.query_id
    HAVING MAX(rs.last_execution_time) < DATEADD (minute, -5, GETUTCDATE())  -- if it has been more than 5 minutes since the adhoc query ran
    ORDER BY q.query_id;
OPEN adhoc_queries_cursor ;
FETCH NEXT FROM adhoc_queries_cursor INTO @id;
WHILE @@fetch_status = 0
BEGIN
    PRINT 'EXEC sp_query_store_remove_query ' + str(@id);
    EXEC sp_query_store_remove_query @id;
    FETCH NEXT FROM adhoc_queries_cursor INTO @id;
END
CLOSE adhoc_queries_cursor;
DEALLOCATE adhoc_queries_cursor;

不要になったデータを消去する別のロジックを利用した独自のプロシージャを定義できます。

前記の例では、sp_query_store_remove_query 拡張ストアド プロシージャを使用して不要なデータを削除しています。 次のこともできます。

• sp_query_store_reset_exec_stats を使用して、指定したプランのランタイム統計情報を消去します。
• sp_query_store_remove_plan を使用して、1 つのプランを削除します。

関連するコンテンツ

• ALTER DATABASE SET オプション (Transact-SQL)
• クエリ ストアのカタログ ビュー (Transact-SQL)
• クエリ ストアのストアド プロシージャ (Transact-SQL)
• インメモリ OLTP でのクエリ ストアの使用
• クエリ処理アーキテクチャ ガイド
• クエリ ストアのヒント
• クエリ ストアを使用したパフォーマンスの監視
• クエリ ストアを使用してパフォーマンスを調整する
• Azure Synapse Analytics での履歴クエリのストレージと分析