クエリ ストアを調べる

SQL Server クエリ ストアは、クエリ、プラン、ランタイム統計の履歴を自動的にキャプチャするデータベースごとの機能であり、パフォーマンスのトラブルシューティングとクエリのチューニングを簡略化します。 また、データベースの使用パターンとリソース消費量に関する分析情報も提供します。

クエリ ストアは、次の 3 つのストアで構成されます。

• プラン ストア: 推定実行プラン情報を格納します。
• ランタイム統計ストア: 実行統計情報を格納します。
• 待機統計ストア: 待機統計情報を保持します。

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

Azure SQL データベースでは、クエリ ストアが既定で有効になります。 SQL Server と Azure Synapse Analytics でそれを使いたい場合は、最初に有効にする必要があります。 クエリ ストア機能を有効にするには、環境に対して有効な次のクエリを使います。

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

-- Azure Synapse Analytics
ALTER DATABASE <database_name> SET QUERY_STORE = ON;

クエリ ストアでデータを収集する方法

クエリ ストアは、複数のステージでクエリ処理パイプラインと統合されます。 各統合ポイントでは、I/O オーバーヘッドを最小限に抑えるために、データがメモリに収集され、ディスクに非同期的に書き込まれます。 統合ポイントは次のとおりです。

1. クエリが初めて実行されるときに、そのクエリ テキストと初期推定実行プランがクエリ ストアに送信されて保存されます。

2. クエリの再コンパイル時に、クエリ ストア内のプランが更新されます。 再コンパイルによって新しい実行プランが生成された場合は、それもクエリ ストアに保存されて、前のプランが拡張されます。 さらに、クエリ ストアでは、比較のために各クエリ プランの実行統計が追跡されます。

3. 再コンパイル フェーズのコンパイルとチェックの間に、クエリ ストアによって、クエリを実行する強制されたプランがあるかどうか確認されます。 クエリ ストアによってプロシージャ キャッシュ内のプランとは異なる強制されたプランが提供されている場合は、クエリが再コンパイルされます。

4. クエリが実行されると、その実行時統計がクエリ ストアに保存されます。 クエリ ストアにこのデータがを集約されることで、すべてのクエリ プランが正確に表現されます。

クエリ ストアでデータが収集される方法について詳しくは、「クエリ ストアによるデータの収集方法」をご覧ください。

一般的なシナリオ

SQL Server クエリ ストアは、データベース操作のパフォーマンスに関する貴重な分析情報を提供します。 一般的なシナリオは、次のとおりです。

• クエリ実行プランの選択の低下によるパフォーマンス低下の特定と修正。
• リソース消費量が最も高いクエリを識別してチューニングする。
• データベースとアプリケーションの変更の影響を評価するための A/B テスト。
• SQL Server のアップグレード後のパフォーマンスの安定性の確保。
• 最も頻繁に使用されるクエリの決定。
• クエリのプラン履歴を監査すること。
• 計画外のワークロードの特定と改善。
• データベースの一般的な待機カテゴリと、待機時間に影響を与えるクエリとプランについて理解します。
• リソース消費量 (CPU、I/O、メモリ) の観点から、時間の経過に伴うデータベースの使用パターンの分析。

クエリ ストアのビューを見つける

データベースでクエリ ストアを有効にすると、データベースのクエリ ストア フォルダーがオブジェクト エクスプローラーに表示されるようになります。 Azure Synapse Analytics の場合、クエリ ストア ビューは [システム ビュー] に表示されます。 クエリ ストア ビューでは、SQL Server データベースのパフォーマンスの側面が簡単にわかる集計された分析情報が提供されます。

後退したクエリ

退行したクエリは、実行プランの変更により、時間の経過とともにパフォーマンスが低下します。 推定実行プランは、スキーマの変更、統計の変更、インデックスの変更など、さまざまな要因によって変更される可能性があります。 プロシージャ キャッシュの調査は最初の本能かもしれませんが、クエリの最新の実行プランのみが格納され、システムのメモリ要求に基づいてプランを削除できます。 ただし、クエリ ストアでは、クエリごとに複数の実行プランが保持されるため、プランの変更によって生じるクエリ パフォーマンスの低下に対処するために 、プランの強制 によって特定のプランを柔軟に選択できます。

[後退したクエリ] ビューでは、指定された期間における実行プランの変更により実行メトリックが低下しているクエリを特定できます。 このビューでは、選択したメトリック (期間、CPU 時間、行数など) と統計 (合計、平均、最小、最大、または標準偏差) に基づいてフィルター処理できます。 次に、提供されたフィルターに基づいて、上位 25 個の後退したクエリが一覧表示されます。 既定では、クエリのグラフィカル横棒グラフ ビューが表示されますが、必要に応じてクエリをグリッド形式で表示することもできます。

左上のクエリ ペインからクエリを選択すると、プランの概要ペインに、クエリに関連付けられている永続化されたクエリ プランが時間の経過と共に表示されます。 [プランの概要] ペインでクエリ プランを選択すると、下部ペインにグラフィカル クエリ プランが表示されます。 プランの概要ペインとグラフィカル クエリ プラン ペインの両方のツール バー ボタンを使用すると、選択したクエリに対して選択したプランを強制的に選択できます。 このペインの構造と動作は、すべての SQL クエリ ビューで一貫して使用されます。

または、sp_query_store_force_plan ストアド プロシージャを使って、プラン強制を使うこともできます。

EXEC sp_query_store_force_plan @query_id=73, @plan_id=79

全体のリソース消費量

[リソース全体の消費量] ビューでは、期間を指定して、複数の実行メトリック (実行数、継続時間、待機時間など) について合計リソース消費量を分析できます。 レンダリングされるグラフは対話型です。いずれかのグラフでメジャーを選ぶと、選んだメジャーに関連付けられているクエリを表示するドリルスルー ビューが新しいタブに表示されます。

詳細ビューには、選んだメトリックの原因になっている上位 25 個のリソース コンシューマー クエリが表示されます。 この詳細ビューで使われている一貫したインターフェイスを使って、関連付けられているクエリとその詳細を検査し、保存されている推定クエリ プランを評価し、必要に応じてプラン強制を使ってパフォーマンスを向上させることができます。 このビューは、CPU 使用率が容量に達したときなど、システム リソースの競合が問題になる場合に有用です。

リソースを多く消費する上位のクエリ

[リソースを消費するクエリの上位] ビューは、[リソース全体の消費量] ビューの詳細ドリルダウンに似ています。 また、メトリックと統計情報をフィルターとして選ぶこともできます。 ただし、表示されるクエリは、選んだフィルターと時間枠に基づいて最も影響が大きい上位 25 件のクエリです。

[ リソース消費の上位クエリ ] ビューでは、計画外のワークロードを特定して改善する際に、ワークロードの計画外の性質を最初に示します。 たとえば、次の図では、[実行回数] メトリックと [合計] 統計が選ばれており、リソース消費量の多いクエリの約 90% が 1 回だけ実行されていることが明らかになっています。

強制適用されたプランのあるクエリ

[強制されたプランを持つクエリ] ビューでは、強制されたクエリ プランのあるクエリを簡単に確認できます。 このビューは、強制されたプランが期待どおりに実行されなくなり、再評価する必要がある場合に関連します。 このビューを使うと、選んだクエリに対して保存されているすべての推定実行プランを確認し、パフォーマンスのために別のプランが適しているかどうかを簡単に判断できます。 その場合は、ツール バーのボタンを使って、必要に応じてプランの強制を解除できます。

高バリエーションのクエリ

クエリのパフォーマンスは、実行によって異なる場合があります。 [高バリエーションのクエリ] ビューには、選んだメトリックの変動つまり標準偏差が最も高いクエリの分析が含まれています。 インターフェイスはクエリ ストアのほとんどのビューと同じであり、クエリの詳細の調査、実行プランの評価、および必要に応じて特定のプランの強制を行うことができます。 このビューを使って、予測できないクエリをより一貫性のあるパフォーマンス パターンになるように調整します。

クエリ待機統計

[クエリ待機統計] ビューでは、データベースの最もアクティブな待機カテゴリが分析されて、グラフがレンダリングされます。 このグラフは対話型です。待機カテゴリを選ぶと、待機時間統計の原因になっているクエリの詳細が表示されます。

詳細ビューのインターフェイスもクエリ ストアのほとんどのビューと同じであり、クエリの詳細の調査、実行プランの評価、および必要に応じて特定のプランの強制を行うことができます。 このビューは、アプリケーション全体でユーザー エクスペリエンスに影響を与えているクエリを特定するのに役立ちます。

追跡クエリ

[追跡中のクエリ] ビューを使うと、入力したクエリ ID の値に基づいて特定のクエリを分析できます。 実行したビューには、クエリの完全な実行履歴が表示されます。 実行のチェックマークは、強制されたプランが使われたことを示します。 このビューでは、強制されたプランがあるものなど、クエリに関する分析情報が提供され、クエリのパフォーマンスが安定していることを確認できます。

クエリ ストアを使用してクエリの待機時間を見つける

システムのパフォーマンスが低下し始めたら、クエリ待機の統計を調べると、原因を特定できる可能性があります。 チューニングが必要なクエリを特定するだけでなく、有益な可能性のあるインフラストラクチャのアップグレードにも光を当てることもできます。

SQL クエリ ストアの [クエリ待機統計] ビューでは、データベースの上位の待機カテゴリに関する分析情報が提供されます。 現在は、23 個の待機カテゴリがあります。

棒グラフには、[クエリ待機統計] ビューを開いた時点でデータベースの対する影響が最も大きい待機カテゴリが表示されます。 さらに、待機カテゴリ ペインのツール バーにあるフィルターを使うと、合計待機時間 (既定)、平均待機時間、最小待機時間、最大待機時間、または標準偏差待機時間に基づいて、待機統計を計算できます。

待機カテゴリを選択すると、その待機カテゴリに影響するクエリの詳細が表示されます。 このビューからは、最も影響が大きい個々のクエリを調査できます。 クエリ ペインでクエリを選ぶと、保存されている推定実行プランが [プランの概要] ペインに表示されます。 [プランの概要] ペインからクエリ プランを選択すると、下部のペインにグラフィカル クエリ プランが表示されます。 このビューから、クエリのクエリ プランを強制または強制解除して、パフォーマンスを向上させることができます。

自動プラン修正

SQL Server 2017 および Azure SQL Database では、クエリ ストア内のデータを分析することにより、自動プラン修正の概念が導入されました。 SQL Server 2017 以降および Azure SQL Database のデータベースで、クエリ ストアを有効にすると、SQL Server エンジンによってクエリ プランの回帰が検索され、レコメンデーションが提供されます。 これらのレコメンデーションは、sys.dm_db_tuning_recommendations 動的管理ビュー (DMV) で確認できます。 これらのレコメンデーションには、パフォーマンスが良好な状態であったときのクエリ プランを手動で強制する T-SQL ステートメントが含まれます。

これらのレコメンデーションで自信を得た場合は、回帰が発生したときに、SQL Server で自動的にプランを強制させることができます。 自動プラン修正を有効にするには、ALTER DATABASE と AUTOMATIC_TUNING 引数を使用します。

Azure SQL Database では、Azure portal または REST API の自動チューニング オプションを使用して、自動プラン修正を有効にすることもできます。 自動プラン修正のレコメンデーションは、クエリ ストアが有効にされている (Azure SQL Database と Azure SQL Managed Instance の既定値) すべてのデータベースで常に有効になります。 新しいデータベースの場合、自動プラン修正 (FORCE_PLAN) は Azure SQL Database で既定で有効にされます。