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

適用対象:yesSQL Server 2016 (13.x) 以降 YesAzure SQL Database YesAzure SQL Managed Instance yesAzure Synapse Analytics (専用 SQL プールのみ)

クエリ ストア機能を使用すると、SQL Server、Azure SQL Database、Azure SQL Managed Instance、および Azure Synapse Analytics のクエリ プランの選択とパフォーマンスに関する分析情報を得ることができます。 クエリ ストアを使用すると、クエリ プランの変更によって生じるパフォーマンスの違いがすばやくわかるようになり、パフォーマンス上のトラブルシューティングを簡略化できます。 クエリのストアは、自動的にクエリ、プラン、および実行時統計の履歴をキャプチャし、確認用に保持します。 データは時間枠で区分されるため、データベースの使用パターンを表示して、サーバー上でクエリ プランが変わった時点を確認することができます。 ALTER DATABASE SET オプションを使用してクエリ ストアを構成できます。

重要

SQL Server 2016 (13.x) の Just In Time ワークロード分析情報のためにクエリ ストアを使用している場合は、KB 4340759 におけるパフォーマンスのスケーラビリティの修正を、できるだけ早くインストールするよう計画します。

クエリ ストアを有効にする

  • クエリ ストアは、新しい Azure SQL Database と Azure SQL Managed Instance データベースに対して既定で有効になっています。
  • クエリ ストアは、SQL Server 2016 (13.x)、SQL Server 2017 (14.x)、SQL Server 2019 (15.x)、SQL Server 2022 (16.x) Preview では既定で有効になりません。 機能を有効にし、より適切にパフォーマンス履歴の追跡、クエリ プランに関連する問題のトラブルシューティング、SQL Server 2022 (16.x) Preview での新機能の有効化を行うために、新規および既存のデータベースでクエリ ストアを有効にすることをお勧めします。
  • クエリ ストアは、新しい Azure Synapse Analytics データベースでは既定で有効になっていません。

SQL Server Management Studio の [クエリ ストア] ページを使う

  1. オブジェクト エクスプローラーで、データベースを右クリックし、 [プロパティ] を選択します。

    注意

    Management Studio のバージョン 16 以降が必要です。

  2. [データベースのプロパティ] ダイアログ ボックスで、 [クエリのストア] ページをクリックします。

  3. [操作モード (要求)] ボックスで、 [読み取り、書き込み] を選択します。

Transact-SQL ステートメントを使用する

ALTER DATABASE ステートメントを使用して、特定のデータベースのクエリ ストアを有効にします。 次に例を示します。

ALTER DATABASE <database_name>
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

Azure Synapse Analytics では、次のように追加のオプションを指定せずにクエリ ストアを有効にします。

ALTER DATABASE <database_name>
SET QUERY_STORE = ON;

クエリ ストアに関連する構文オプションの詳細については、「ALTER DATABASE SET オプション (Transact-SQL)」を参照してください。

注意

master データベースまたは tempdb データベースに対しては、クエリ ストアを有効にできません。

重要

クエリ ストアを有効にして、ワークロードに合わせて調整された状態を維持するための情報については、「クエリ ストアを使用するときの推奨事項」を参照してください。

クエリのストア内の情報

SQL Server のどの特定のクエリの実行プランも、通常、統計情報やスキーマの変更、インデックスの作成または削除などのさまざまな理由により、時間の経過とともに進化します。プロシージャ キャッシュ (ここにキャッシュされたクエリ プランが格納される) には、最新の実行プランのみ格納されます。 メモリ負荷が原因で、プランがプラン キャッシュから削除されることもあります。 その結果、実行プランの変更によるクエリ パフォーマンスの低下が深刻なレベルになり、解決に時間を要する場合があります。

クエリ ストアには、1 つのクエリにつき複数の実行プランが保持されるため、クエリの特定の実行プランを使用するようクエリ プロセッサに指示するポリシーを強制できます。 これをプラン強制と呼びます。 クエリのストアのプラン強制は、 USE PLAN クエリ ヒントに似たメカニズムを使用して提供されますが、ユーザー アプリケーションを変更する必要はありません。 プラン強制を使用することで、プラン変更によるクエリ パフォーマンスの低下をきわめて短時間に解決できます。

注意

クエリ ストアでは、SELECT、INSERT、UPDATE、DELETE、MERGE、BULK INSERT などの DML ステートメントのプランが収集されます。

既定では、ネイティブ コンパイルされるストアド プロシージャのデータがクエリ ストアで収集されることはありません。 ネイティブ コンパイルされるストアド プロシージャのデータを収集するには、sys.sp_xtp_control_query_exec_stats を使用してください。

待機統計は、データベース エンジン のパフォーマンスのトラブルシューティングに役立つもう 1 つの情報源です。 長い間、待機統計はインスタンス レベルでしか使うことができず、待機を特定のクエリにバックトラックするのは困難でした。 SQL Server 2017 (14.x) および Azure SQL データベース 以降、待機状態を追跡するディメンションがクエリ ストアに含まれます。次の例では、クエリ ストアで待機状態の収集が有効になります。

ALTER DATABASE <database_name>
SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );

このクエリのストアの機能を使用する一般的なシナリオは次のとおりです。

  • 前のクエリ プランを強制的に適用することにより、プラン パフォーマンスの低下をすばやく発見し修正します。 実行プランの変更によって最近パフォーマンスが低下したクエリを修正します。
  • 特定の時間枠内にクエリが実行された回数を確認し、パフォーマンス リソースの問題に関するトラブルシューティングにおいて DBA を支援します。
  • 上位 n クエリ (過去 x 時間内) を、実行時間やメモリ消費量などを基に識別します。
  • 指定したクエリのクエリ プランの履歴を監査します。
  • 特定のデータベースのリソース (CPU、I/O、メモリ) の使用パターンを分析します。
  • リソースで待機している上位 n クエリを識別します。
  • 特定のクエリまたはプランの待機の性質を理解します。

クエリ ストアには 3 つのストアが含まれます。

  • プラン ストアは、実行プラン情報の保存用です。
  • ランタイム統計ストアは、実行統計情報の保存用です
  • 待機統計ストアは、待機統計情報の保存用です

クエリのためにプラン ストア内に格納できる一意のプラン数は、 max_plans_per_query 構成オプションによって制限されています。 パフォーマンスを向上させるために、この情報はストアに非同期的に書き込まれます。 領域使用量を最小にするため、ランタイム統計情報ストアのランタイム実行統計情報は、一定の時間枠で集計されます。 これらのストア内の情報は、クエリのストアのカタログ ビューに対してクエリを実行することによって表示できます。

次のクエリは、クエリのストア内のクエリとプランに関する情報を返します。

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl
INNER JOIN sys.query_store_query AS Qry
    ON Pl.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id;

セカンダリ レプリカのクエリ ストア

適用対象: SQL Server (SQL Server 2022 (16.x) Preview 以降)

セカンダリ レプリカのクエリ ストア機能により、プライマリ レプリカで使用できるセカンダリ レプリカ ワークロードで同じクエリ ストア機能が有効になります。 セカンダリ レプリカのクエリ ストアが有効になっている場合、レプリカにより、通常はクエリ ストアに格納されるクエリ実行情報がプライマリ レプリカに送信されます。 その後、プライマリ レプリカにより、独自のクエリ ストア内のディスクにデータが保持されます。 本質的には、プライマリおよびすべてのセカンダリ レプリカの間で共有されるクエリ ストアは 1 つです。 クエリ ストアはプライマリ レプリカに存在し、すべてのレプリカのデータが一緒に格納されます。

注意

レプリカ セットまたはレプリカ グループ: レプリカ セットは、ロール (プライマリ、セカンダリ、geo セカンダリ、geo プライマリ) を共有するすべての名前のないレプリカ、または個々の名前付きレプリカとして定義されます。

クエリに関して格納されるデータは、レプリカ セット ベースでワークロードとして分析できます。 レプリカのクエリ ストアでは、セカンダリ レプリカに対して実行される可能性がある一意の読み取り専用ワークロードのパフォーマンスを監視および調整できます。

セカンダリ レプリカのクエリ ストアを有効にする

セカンダリ レプリカのクエリ ストアを使用する前に、Always On 可用性グループを設定して構成する必要があります。

重要

適用対象: SQL Server 2022 (16.x) CTP 2.x

セカンダリ レプリカのクエリ ストアを有効にする前に、一連のトレース フラグ (12606、12606、12607、12608、12610、T12624) を有効にする必要があります。 これらのトレース フラグを有効にするには:

  1. サービス管理コンソール ([実行] メニューから services.msc) を開きます。
  2. SQL Server 2022 CTP 2 の [SQL Server] サービスを右クリックし、[プロパティ] を選択します。
  3. サービスの状態が [実行中] の場合は、[停止] を選択します。 これにより、インストールされているインスタンスが停止します。
  4. [開始パラメーター] ボックスに、-T12606 -T12607 -T12608 -T12610 -T12624 の値を追加します。
  5. [開始] を選択してサービスを開始します。
  6. [OK] を選択します。

ALTER DATABASE SET オプション (Transact-SQL) を使用して、セカンダリ レプリカのクエリ ストアを有効にします。 次の例では、プライマリ データベースでクエリ ストアを有効にしてから、セカンダリ レプリカで有効にします。 このコードを実行するには、プライマリ レプリカ上のデータベースに接続します。

ALTER DATABASE CURRENT SET QUERY_STORE = ON;
GO

ALTER DATABASE CURRENT  
FOR SECONDARY SET QUERY_STORE = ON ( 
        OPERATION_MODE = READ_WRITE 
);
GO

セカンダリ レプリカ上のデータベースに接続し、次の Transact-SQL を実行することで、セカンダリ レプリカでクエリ ストアが有効になっていることを検証できます。

SELECT desired_state, desired_state_desc, actual_state, actual_state_desc, readonly_reason
FROM sys.database_query_store_options;
GO

以下の sys.database_query_store_options のクエリ結果のサンプルは、クエリ ストアがセカンダリの読み取り/書き込み状態にあることを示しています。 8 の readonly_reason は、セカンダリ レプリカに対してクエリが実行されたことを示します。 これらの結果は、セカンダリ レプリカでクエリ ストアが正常に有効になっていることを示します。

desired_state desired_state_desc actual_state actual_state_desc readonly_reason
2 READ_WRITE 2 READ_WRITE 8

セカンダリ レプリカのクエリ ストアのパフォーマンスに関する考慮事項

セカンダリ レプリカによってクエリ情報をプライマリ レプリカに送信するために使用されるチャネルは、セカンダリ レプリカを最新の状態に保つために使用されるチャネルと同じです。 データは、プライマリ レプリカで実行されるクエリにクエリ ストアによって使用されるプライマリ レプリカ上の同じテーブルに格納されるため、クエリ ストアのサイズが大きくなります。

したがって、システムに大きな負荷がかかっている場合は、チャネルが過負荷になっているため、速度が低下する可能性があります。 さらに、現在クエリ ストアに存在する同じアドホック クエリ キャプチャの問題は、セカンダリ レプリカで実行されるワークロードで引き続き発生します。 詳細については、「最も重要なデータをクエリ ストアに保存する」を参照してください。

セカンダリ レプリカのクエリ ストアを無効にする

セカンダリ レプリカのクエリ ストアを無効にするには、プライマリ レプリカ上のデータベースに接続し、次のコードを実行します。

ALTER DATABASE CURRENT  
FOR SECONDARY SET QUERY_STORE = OFF;
GO

機能低下したクエリ機能を使用する

クエリのストアを有効にしてから、[オブジェクト エクスプローラー] ペインのデータベースの部分を更新して、 [クエリ ストア] セクションを追加します。

SQL Server 2016 Query Store tree in SSMS Object ExplorerSQL Server 2017 Query Store tree in SSMS Object Explorer

注意

Azure Synapse Analytics の場合、クエリ ストア ビューは、オブジェクト エクスプローラー ペインのデータベース部分の [システム ビュー] で使用できます。

SQL Server Management Studioで [機能低下したクエリ] を選択し、 [機能低下したクエリ] ペインを開きます。 [機能低下したクエリ] ペインにクエリと、クエリのストア内のプランが表示されます。 上部のドロップダウン ボックスを使用し、さまざまな条件に基づいてクエリをフィルター処理します: 実行時間 (ミリ秒) (既定)、CPU 時間 (ミリ秒)、論理読み取り (KB)、論理書き込み (KB)、物理読み取り (KB)、CLR 時間 (ミリ秒)、DOP、メモリ消費量 (KB)、行数、使用済みログ メモリ (KB)、使用済み一時 DB メモリ (KB)、待機時間 (ミリ秒)。

プランを選択して、グラフィカルなクエリ プランを表示します。 ボタンを使用して、ソース クエリの表示、クエリ プランの強制と強制解除、グリッド形式とグラフ形式の切り替え、選択したプランの比較 (複数選択時)、表示の更新を行うことができます。

SQL Server 2016 Regressed Queries in SSMS Object Explorer

プランを強制的に適用するには、クエリとプランを選択してから、 [プランの強制] を選択します。 強制できるプランは、クエリ プランの機能によって保存され、クエリ プランのキャッシュに保持されているプランのみです。

待機クエリを見つける

SQL Server 2017 (14.x) および Azure SQL データベース 以降では、クエリごとの時系列の待機統計情報をクエリ ストアで使用できます。

クエリ ストアでは、待機の種類が待機カテゴリに組み合わされます。 待機カテゴリから待機の種類へのマッピングについては、「sys.query_store_wait_stats (Transact-SQL)」を参照してください。

SQL Server Management Studio v18 以降では、 [クエリ待機統計] を選択して [クエリ待機統計] ペインを開きます。 [クエリ待機統計] ペインには、クエリ ストアで上位の待機カテゴリを含む棒グラフが表示されます。 上部のドロップダウンを使用して、待機時間の集計条件を選択します (平均、最大、最小、標準偏差、合計 (既定値))。

SQL Server 2017 Query Wait Statistics in SSMS Object Explorer

棒をクリックして待機カテゴリを選択すると、選択した待機カテゴリの詳細ビューが表示されます。 この新しい棒グラフには、その待機カテゴリの原因になっているクエリが含まれます。

SQL Server 2017 Query Wait Statistics detail view in SSMS Object Explorer

上部のドロップダウン ボックスを使用して、選択した待機カテゴリのさまざまな待機時間条件に基づいてクエリをフィルター処理します (平均、最大、最小、標準偏差、合計(既定値))。 プランを選択して、グラフィカルなクエリ プランを表示します。 ソース クエリの表示、クエリ プランの強制と強制解除、表示の更新に使用できるボタンが用意されています。

待機カテゴリでは、異なる待機種類が性質の類似性によってバケットに組み合わされます。 問題の解決に必要なフォローアップ分析は待機カテゴリによって異なりますが、同じカテゴリの待機種類からは非常によく似たトラブルシューティング エクスペリエンスが得られ、待機の先頭に影響受けたクエリを提供することは、このような調査のほとんどを正常に完了するために不足している部分です。

クエリ ストアに待機カテゴリが導入される前後でのワークロードの詳細情報の取得方法の例を次に示します。

以前のエクスペリエンス 新しいエクスペリエンス アクション
データベースごとの高い RESOURCE_SEMAPHORE 待機 特定のクエリに対するクエリ ストアでの高いメモリ待機 クエリ ストア内で、メモリを最も消費しているクエリを探します。 おそらくこれらのクエリによって、影響を受けているクエリの進行がさらに遅れています。 これらのクエリまたは影響を受けるクエリに、MAX_GRANT_PERCENT クエリ ヒントを使うことを検討します。
データベースごとの高い LCK_M_X 待機 特定のクエリに対するクエリ ストアでの高いロック待機 影響を受けているクエリのクエリ テキストを確認し、ターゲット エンティティを識別します。 クエリ ストアで同じエンティティを変更している他のクエリを探します。これらは、頻繁に実行されていたり、実行時間が長くなったりします。 これらのクエリを特定した後で、コンカレンシーを向上させるためにアプリケーション ロジックを変更するか、より制限の低い分離レベルを使用します。
データベースごとの高い PAGEIOLATCH_SH 待機 特定のクエリに対するクエリ ストアでの高いバッファー IO 待機 クエリ ストア内で物理読み取り回数が多いクエリを検索します。 それらが IO 待機の長いクエリと一致する場合は、スキャンではなくシークを行うように基になるエンティティにインデックスを導入して、クエリの IO オーバーヘッドを最小限に抑えることを検討します。
データベースごとの高い SOS_SCHEDULER_YIELD 待機 特定のクエリに対するクエリ ストアでの高い CPU 待機 クエリ ストアで CPU 消費量の多いクエリを探します。 それらの中で、高い CPU 傾向が影響を受けるクエリの高い CPU 待機と関連性のあるクエリを特定します。 それらのクエリの最適化に注目します。プラン回帰または欠落インデックスが存在する可能性があります。

構成オプション

クエリ ストア パラメーターを構成するために使用できるオプションについては、「ALTER DATABASE SET オプション (Transact-SQL)」を参照してください。

sys.database_query_store_options ビューに対してクエリを実行し、クエリ ストアの現在のオプションを確認します。 値に関する詳細については、「sys.database_query_store_options」を参照してください。

Transact-SQL ステートメントを使用して構成オプションを設定する方法の例については、「オプション管理」をご覧ください。

Note

Azure Synapse Analytics では、他のプラットフォームと同じようにクエリ ストアを有効にできますが、追加の構成オプションはサポートされていません。

クエリのストアは、Management Studio か、次のビューとプロシージャを使用して表示および管理します。

クエリ ストア関数

関数は、クエリ ストアの操作に役立ちます。

クエリ ストアのカタログ ビュー

カタログ ビューはクエリのストアの情報を提供します。

クエリ ストアのストアド プロシージャ

ストアド プロシージャはクエリのストアを構成します。

sp_query_store_consistency_check (Transact-SQL)1

1 極端なシナリオでは、クエリ ストアが内部エラーのためにエラー状態になることがあります。 SQL Server 2017 (14.x) 以降では、これが発生した場合、影響を受けたデータベース内で sp_query_store_consistency_check ストアド プロシージャを実行することで、クエリ ストアを復旧させることができます。 actual_state_desc 列の詳細については、「sys.database_query_store_options」を参照してください。

主な使用シナリオ

オプション管理

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

クエリ ストアの状態

クエリ ストアではユーザー データベース内にデータが格納されるため、サイズに上限が設定されています (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 (Transact-SQL)」を参照してください。

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

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

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 分。

注意

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 では、クエリ ストアの消去はできません。 過去 30 日間のデータが自動的に保持されます。

アドホック クエリの削除

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

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 つのプランを削除します。

パフォーマンスの監査とトラブルシューティング

クエリ ストアを使用したパフォーマンス調整の詳細については、「クエリ ストアを使用してパフォーマンスを調整する」を参照してください。

その他のパフォーマンスに関するトピック:

関連項目

次の手順