クエリ ストアを使用したパフォーマンスの監視

  • [アーティクル]

適用対象: SQL Server 2016 (13.x) 以降 Azure SQL データベース Azure SQL Managed Instance Azure 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) 以降の新しいデータベースの READ_WRITE モードでは、既定で有効になっています。 機能を有効にし、より適切にパフォーマンス履歴の追跡、クエリ プランに関連する問題のトラブルシューティング、SQL Server 2022 (16.x) での新機能の有効化を行うために、データベースでクエリ ストアを有効にすることをおすすめします。
  • クエリ ストアは、新しい Azure Synapse Analytics データベースでは既定で有効になっていません。

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

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

    Note

    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)」を参照してください。

Note

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

重要

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

クエリのストア内の情報

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

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

Note

クエリ ストアは、SELECT、INSERT、UPDATE、DELETE、MERGE、および BULK INSERT などの DML ステートメントのプランを収集します。

設計上、クエリ ストアでは、CREATE INDEX などの DDL ステートメントのプランは収集されません。クエリ ストアは、基になる DML ステートメントのプランを収集することによって累積リソース消費量をキャプチャします。 たとえば、クエリ ストアでは、内部的に実行される SELECT ステートメントと INSERT ステートメントを表示して、新しいインデックスを設定できます。

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

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

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, Pln.plan_id, Qry.*, RtSt.*
FROM sys.query_store_plan AS Pln
INNER JOIN sys.query_store_query AS Qry
    ON Pln.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id
INNER JOIN sys.query_store_runtime_stats RtSt
ON Pln.plan_id = RtSt.plan_id;

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

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

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

セカンダリ レプリカのクエリ ストアの詳細については、「Always On 可用性グループのセカンダリ レプリカのクエリ ストア」を参照してください。

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

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

SSMS オブジェクト エクスプローラーのクエリ ストア レポート ツリーのスクリーンショット。

Note

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

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

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

SSMS オブジェクト エクスプローラーでの回帰したクエリを示すスクリーンショット。

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

待機クエリを見つける

SQL Server 2017 (14.x) 以降および Azure SQL Database では、クエリ ストアのユーザーは一定期間のクエリごとの待機統計を見ることができます。

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

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

SSMS オブジェクト エクスプローラーでの SQL Server クエリの待機統計を示すスクリーンショット。

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

SSMS オブジェクト エクスプローラーでの SQL Server クエリの待機統計の詳細ビューを示すスクリーンショット。

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

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

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

以前のエクスペリエンス 新しいエクスペリエンス アクション
データベースごとの高い 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 か、次のビューとプロシージャを使用して表示および管理します。

クエリ ストア関数

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

sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)

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

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

sys.database_query_store_options (Transact-SQL)

sys.query_context_settings (Transact-SQL)

sys.query_store_plan (Transact-SQL)

sys.query_store_query (Transact-SQL)

sys.query_store_query_text (Transact-SQL)

sys.query_store_runtime_stats (Transact-SQL)

sys.query_store_wait_stats (Transact-SQL)

sys.query_store_runtime_stats_interval (Transact-SQL)

sys.database_query_store_internal_state (Transact-SQL)

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

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

sp_query_store_flush_db (Transact-SQL)

sp_query_store_reset_exec_stats (Transact-SQL)

sp_query_store_force_plan (Transact-SQL)

sp_query_store_unforce_plan (Transact-SQL)

sp_query_store_remove_plan (Transact-SQL)

sp_query_store_remove_query (Transact-SQL)

sp_query_store_clear_message_queues (Transact-SQL)

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」を参照してください。

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

クエリ ストアのメンテナンスと管理に関するベスト プラクティスと推奨事項については、クエリ ストアを管理するためのベスト プラクティスに関する記事を参照してください。

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

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

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

関連するコンテンツ