診断系の情報を取得するクエリはいくつか持っているのですが、クエリについての情報を拡充したかったので、考えを整理するためのメモを。
■プランの種類を把握する
クエリ実行方法にはいくつかあり、大きく分けると
- ストアドプロシージャ (Proc)
- 準備済みステートメント (Prepared)
- アドホック クエリ (Adhoc)
となり、情報については sys.dm_exec_cached_plans から取得することができます。
SQL Server では、簡易パラメーター化 / 強制パラメーター化 の設定があり、アドホック クエリを自動的に準備済みステートメントに変換する機能がありますが、必ずしも変換されるわけではないので、基本的な考え方としては意識してストアドプロシージャ / 準備済みステートメントで実行するようにしておくとよいかと。
■リソースの使用状況の高いクエリを把握する
リソースの使用状況としては、
- CPU 時間 (Worker Time)
- 実行完了までの経過時間 (Elapsed Time)
- 物理読み込み (Physical Read) / 物理書き込み (Physical Write)
- 論理読み込み (Logical Read) / 論理書き込み (Logical Write)
- 実行回数 (Execution Count)
があり、情報については sys.dm_exec_query_stats から取得することができます。
観点によってどれが高い値を示しているかを変えながら調査する必要があります。
■コンパイルの状況を把握する
コンパイルの状況でポイントとなるのが、
- コンパイル時に使用されたパラメーター (Parameter List)
- コンパイルされたタイミング (Creation Time)
- 再コンパイルの発生状況 (Plan Generation Num)
があるかと。
再利用されるクエリについては初回実行時にコンパイルされ作成された実行プランが使用されます。
その実行プランが作成されたときに指定された引数 (パラメーター) は実行プランの Parameter List から確認することができます。
プランが作成されたタイミング = コンパイル (またはリコンパイル) されたタイミングとなりますので、直近の統計情報を使用してコンパイルされたかを sys.dm_exec_query_stats から確認することも重要となります。
Plan Generation Num が 1 より大きい場合は該当のクエリが再コンパイルされていることになります。
■統計情報の状態
プランは統計情報をもとに生成されるため、統計情報が最新の状態に近い (かい離していない) が重要となります。
統計情報の更新が行われていない場合、データのヒストグラムが現状と異なっているため予期しないプランになる可能性があります。
統計情報の更新状況は STATS_DATE 関数で確認することができます。