クエリ ストアを調べる
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 オーバーヘッドを最小にするため、データがメモリに収集されて、非同期的にディスクに書き込まれます。 統合ポイントは次のとおりです。
クエリが初めて実行されるときに、そのクエリ テキストと初期推定実行プランがクエリ ストアに送信されて保存されます。
クエリが再コンパイルされると、クエリ ストア内のプランが更新されます。 再コンパイルによって新しい実行プランが生成された場合は、それもクエリ ストアに保存されて、前のプランが拡張されます。 さらに、クエリ ストアでは、比較のために各クエリ プランの実行統計が追跡されます。
再コンパイル フェーズのコンパイルとチェックの間に、クエリ ストアによって、クエリを実行する強制されたプランがあるかどうか確認されます。 クエリ ストアによってプロシージャ キャッシュ内のプランとは異なる強制されたプランが提供されている場合は、クエリが再コンパイルされます。
クエリが実行されると、その実行時統計がクエリ ストアに保存されます。 クエリ ストアにこのデータがを集約されることで、すべてのクエリ プランが正確に表現されます。
クエリ ストアでデータが収集される方法について詳しくは、「クエリ ストアによるデータの収集方法」をご覧ください。
一般的なシナリオ
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 個の待機カテゴリがあります。
棒グラフには、[クエリ待機統計] ビューを開いた時点でデータベースの対する影響が最も大きい待機カテゴリが表示されます。 さらに、待機カテゴリ ペインのツール バーにあるフィルターを使うと、合計待機時間 (既定)、平均待機時間、最小待機時間、最大待機時間、または標準偏差待機時間に基づいて、待機統計を計算できます。
待機カテゴリを選ぶと、その待機カテゴリの原因になっているクエリの詳細にドリルスルーされます。 このビューからは、最も影響が大きい個々のクエリを調査できます。 クエリ ペインでクエリを選ぶと、保存されている推定実行プランが [プランの概要] ペインに表示されます。 [プランの概要] ペインでクエリ プランを選ぶと、下部ペインにクエリ プランがグラフィカルに表示されます。 このビューから、クエリのクエリ プランを強制または強制解除して、パフォーマンスを向上させることができます。
