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

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

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

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

Note

SQL Server 2022 (16.x) では、新しく作成されたすべての SQL Server データベースに対してクエリ ストアが既定で有効になり、パフォーマンス履歴の追跡、クエリ プラン関連の問題のトラブルシューティング、新しいクエリ プロセッサ機能の有効化に役立ちます。

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

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

構成	説明	Default	コメント
MAX_STORAGE_SIZE_MB	クエリ ストアがユーザーのデータベース内で使用するデータ領域の制限を指定します。	100 SQL Server 2019 (15.x) より前のバージョン 1000 SQL Server 2019 (15.x) 以降	新しいデータベースに適用
INTERVAL_LENGTH_MINUTES	クエリ プランで収集されたランタイム統計が集計されて保存される間隔を定義します。 すべてのアクティブなクエリ プランには、この構成で定義された期間の行が最大で 1 行含まれます。	60	新しいデータベースに適用
STALE_QUERY_THRESHOLD_DAYS	保存されたランタイム統計と非アクティブなクエリのリテンション期間を制御する、時間に基づくクリーンアップ ポリシー	30	新しいデータベースと前の既定値 (367) を持つデータベースに適用
SIZE_BASED_CLEANUP_MODE	クエリ ストアのデータ サイズが制限値に近づいたときに、データの自動クリーンアップが発生するかどうかを指定します	AUTO	すべてのデータベースに適用
QUERY_CAPTURE_MODE	すべてのクエリを追跡するか、クエリのサブセットのみを追跡するかを指定します	AUTO	すべてのデータベースに適用
DATA_FLUSH_INTERVAL_SECONDS	キャプチャされたランタイム統計がディスクにフラッシュされる前に、メモリ内に保持される最大期間を指定します	900	新しいデータベースに適用

重要

上記の既定値は、すべての Azure SQL Database のクエリ ストアのアクティブ化の最終段階で自動的に適用されます。 有効にされた後、ユーザーによって設定される構成値は、主要なワークロードまたはクエリ ストアの信頼できる動作に悪影響を与えない限り、Azure SQL Database によって変更されることはありません。

Note

Azure SQL データベース 単一データベースとエラスティック プールでは、クエリ ストアを無効にすることはできません。 ALTER DATABASE [database] SET QUERY_STORE = OFF を実行すると、警告 'QUERY_STORE=OFF' is not supported in this version of SQL Server. が返されます

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

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

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

Query Store Capture Mode (クエリ ストアのキャプチャ モード)	シナリオ
すべて	すべてのクエリの図形とその実行頻度やその他の統計情報の観点から、ワークロードを詳しく分析します。 ワークロード中の新しいクエリを特定します｡ アドホック クエリを使用してユーザーまたは自動化によるパラメーター化が特定されているかどうかを検出します。 注: これは、SQL Server 2016 (13.x) および SQL Server 2017 (14.x) の既定のキャプチャ モードです。
Auto	関連するクエリと実用的なクエリに焦点を絞ります。 たとえば、定期的に実行されるクエリや、大量のリソースを消費するクエリなどがあります。 注: SQL Server 2019 (15.x) 以降のバージョンでは、これは既定のキャプチャ モードです。
なし	実行時に監視する必要があるクエリ セットを既にキャプチャしており、他のクエリによって生じる可能性のある、集中を妨げるものを取り除きたいと考えています。 None は、テストおよびベンチマーク環境に適しています。 このモードは、アプリケーションのワークロードを監視するよう構成したクエリ ストアの構成を販売するソフトウェア ベンダーにも適しています。 重要な新しいクエリを追跡して最適化する機会を見逃す可能性があるため、None を使用する際は注意してください。 シナリオで必要な特別な場合を除き、このモードは使用しないでください。
カスタム	SQL Server 2019 (15.x) では、ALTER DATABASE ... SET QUERY_STORE コマンドの下にカスタム キャプチャ モードが導入されました。 オートが既定でおすすめである一方、クエリ ストアによって発生する可能性があるオーバーヘッドが気になる場合、データベース管理者はカスタム キャプチャ ポリシーを使用して、クエリ ストアでのキャプチャの内容をさらにチューニングできます。 詳細とおすすめについては、この記事の後半の 「カスタム キャプチャ ポリシー」 を参照してください。 この構文に関して詳しくは、「ALTER DATABASE SET オプション」をご覧ください。

Note

クエリ ストア キャプチャ モードが All、Auto、または Custom に設定されている場合、カーソル、ストアド プロシージャ内のクエリ、ネイティブ コンパイル済みのクエリは常にキャプチャされます。 ネイティブ コンパイル済みのクエリをキャプチャするには、sys.sp_xtp_control_query_exec_stats を使用して、クエリごとの統計情報の収集を有効にします。

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

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

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

ベスト プラクティス	設定
保存する履歴データに制限を設ける。	自動クリーンアップを有効にするように時間ベースのポリシーを構成します。
関連しないクエリを除外する。	[クエリ ストア キャプチャ モード] を Auto に設定します。
最大サイズに達したときに、関連性の低いクエリを削除する。	サイズ ベースのクリーンアップ ポリシーを有効にします。

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

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

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

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

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

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

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

SSMS

最新バージョンの SQL Server Management Studio (SSMS) をダウンロードします

Management Studio で現在の設定を表示するには:

1. SQL Server Management Studio オブジェクト エクスプローラーで、データベースを右クリックします。
2. プロパティを選択します。
3. クエリ ストアを選択します。 [クエリ ストア]ページで、操作モード (要求) が読み取り書き込みであることを確認します。
4. クエリ ストア キャプチャ モードをカスタムに変更します。
5. クエリ ストア キャプチャ ポリシーの下の 4 つのキャプチャ ポリシー フィールドが有効になり、構成可能になったことに注意してください。

T-SQL

ALTER DATABASE を使用... 構文を、QUERY_CAPTURE_MODE = CUSTOM に設定し、QUERY_CAPTURE_POLICY のオプションを指定します。 カスタム キャプチャ ポリシー オプションの詳細については、以下の T-SQL の例と ALTER DATABASE SET オプション (Transact-SQL) を参照してください。

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

次の例では、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 への影響がスムーズになる可能性がありますが、データは少なくなります。

Note

トレース フラグ 7745 を使用すると、フェールオーバーまたはシャットダウン コマンドが発生した場合に、クエリ ストアのデータはディスクに書き込まれません。 詳細については、「ミッション クリティカルなサーバーでクエリ ストアを使用する」を参照してください。

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

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

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

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

SSMS

最新バージョンの SQL Server Management Studio (SSMS) をダウンロードします

Management Studio で現在の設定を表示するには:

1. SQL Server Management Studio オブジェクト エクスプローラーで、データベースを右クリックします。
2. プロパティを選択します。
3. クエリ ストアを選択します。

T-SQL

クエリ ストアのサイズに関する最新の情報を取得するには、次の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 の場合、クエリがクエリ ストアによるキャプチャの対象となります。

重要

クエリ ストア キャプチャ モードが All、Auto、または Custom に設定されている場合、カーソル、ストアド プロシージャ内のクエリ、ネイティブ コンパイル済みのクエリは常にキャプチャされます。 ネイティブ コンパイル済みのクエリをキャプチャするには、sys.sp_xtp_control_query_exec_stats を使用して、クエリごとの統計情報の収集を有効にします。

次のスクリプトでは、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 分のいずれかのサイクル間隔を使用します。

Note

Azure Synapse Analytics では、このセクションで説明しているクエリ ストアの構成オプションのカスタマイズはサポートされていません。

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

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

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 での履歴クエリのストレージと分析