簡単な PowerShell のスクリプトですが Collect-QueryInfo というツールを公開しました。
このツールを使用すると、定期的に DMV の情報を SQL Server のデータベースに格納することができます。
これによりどのような情報の解析が可能となるかを見ていきたいと思います。
情報の取得元の SQL Server のバージョンは SQL Server 2012 以降となっています。
Collect-QueryInfo のアプローチは「連続した面の情報により線を作る」というものになります。
DMV で取得できる情報はスナップショット的な情報となり、「情報取得タイミングの面 (断面) の情報」となります。
この情報も重要ではあるのですが、SQL Server の状況を確認する中で、「時系列データ」としての情報が必要となることがあります。
「インデックスの使用状況」で考えてみます。
インデックスの使用状況は「sys.dm_db_index_operational_stats」「sys.dm_db_index_usage_stats」で取得をすることができますが、これらの DMV で取得できる値は、SQL Server のサービスを最後に起動してからの累計値となります。
「どのインデックスが頻繁に使用されているか」を確認することはできるのですが、単一の情報の取得では、「xx 時にどのインデックスが何回使用されたのか」までは把握することはできません。
「xx 時にどのインデックスが何回使用されたのか」の情報を取得したい場合には、定期的に DMV の情報をダンプし、前回取得時間との差分を抽出する必要があります。
この「定期的に DMV の情報をダンプ」するのが「Collect-QueryInfo」となります。
使用方法はシンプルで、「Collect-QueryInfo.ps1」を実行するだけです。
接続の情報や、実行に使用するクエリのパスがスクリプト内に記載されているので次の個所を変更してください。
- $collectFilePath
- 情報の取得に使用する SQL を格納しているディレクトリ
- 指定したパス内のサブディレクトリを含んだ階層内で、「Collect.sql」を実行対象とする
- $collectTableList
- 取得した Collect.sql の情報を格納するテーブル名
- CollectFilePath で取得したファイルリストの順と対応した名称とする
- $collectIntervalList
- Collect.sql の実行間隔 (秒)
- CollectFilePath で取得したファイルリストの順と対応した名称とする
- $srcCon_string
- 情報の取得対象となる SQL Server の接続文字列
- インデックスの取得情報は「Initial Catalog」に指定したデータベースに対して実行される
- $destCon_string
- 取得した情報を格納する SQL Server の接続文字列
- 「Initial Catalog」に指定したデータベースに情報が格納される
これらの情報を適宜変更し、PowerShell を実行することで情報の取得が開始されます。
(PowerShell のジョブとして、各スクリプトがバックグラウンドで実行されます。Runspace を使っていないのはサクッと組みたかったらの手抜きだったりします…)
情報は SQL Server のデータベース内のテーブルとして取得が行われるのですが、取得先については「SQL Server 2016 SP1 以降」を使用するようにしてください。
ログは大量の行となるため、各テーブルはページ圧縮を使用しています。
SQL Server 2016 SP1 以降であれば、Express Edition でもデータ圧縮を使用することができますので、ログによるストレージサイズを削減することができます。
情報の取得を終了したい場合は「Ctrl+C」でスクリプトを中断してください。
取得した情報は、各情報に対応した「Result.sql」を実行することで情報を成型することができます。
インデックスの使用状況の情報であれば、次のような結果を取得することができます。 
この情報から「特定の時間帯にアクセスが行われたインデックスと操作の回数」を取得することができます。
この情報をもとに、PowerBI や Excel でグラフ化を行うことで「各インデックスがどの時間帯にどの程度アクセスされたか」という情報を把握することができます。
「バッチ実行数」の Result.sql でも普段ではあまり見ないような情報を確認することができます。
SQL Server に対してのクエリ実行数を把握したい場合「Batch Requests/sec」の時系列データで確認することができるのは一般的な話かと思います。
この時系列データに「Batch Resp Statistics」の時系列データを組み合わせると「実行されたバッチを「実行にかかった時間の分布」で詳細化」するというようなアプローチが可能となります。
波形としては「Batch Requests/sec」と同じなのですが、「実行にかかった時間の分布」で分類された積み上げグラフとなっていますので、該当の時間帯に実行されたバッチが「短時間で完了しているクエリなのかどうか」ということを判断することができます。
「Batch Request/sec」「Batch Resp Statistics」は、パフォーマンスモニターを使用することで時系列データとして使用することができますが、パフォーマンスモニターによりアクセスができない環境というものも存在しますので、その際には、DMV の情報を連続して取得を行うことで対応を行います。
待ち事象の情報についても、DMV の情報を時系列にすることで「どの待ち事象がどの時間帯に発生したか」という解析を詳細に行うことができます。 
投稿を書いている時点では、
- インデックス使用状況
- バッチ実行数
- 待ち事象
の 3 種類についての情報を取得するものとなっていますが、追加で取得したい情報があれば、クエリの追加とスクリプト一部修正で対応できるかと思います。
DMV を時系列データとして使用するアプローチは一度仕組みを作ってしまえば、汎用的に使用できるものとなりますので、自分の作りやすい言語で一度ツール化してみるとよいかと。