SE の雑記

以前からいくつかクエリストアの情報は書いていましたが、最近また、クエリストアを使いだしたので、ちょっと情報をまとめてみたいと思います。

■クエリストアから特定のクエリ情報を探す

SSMS では、クエリストアの情報からいくつかのパターンで情報を確認することができるビューがデフォルトで含まれています。
# SSMS 17.0 CTP2 だと、項目が増えているようですね。

高いリソース消費のクエリなどについては標準のビューで確認すればよいかと思いますが、特定のクエリの情報を確認したい場合もあると思います。

そのような場合は「追跡したクエリ」から確認ができます。
「追跡したクエリ」の虫眼鏡アイコンをクリックすると、検索条件を指定できるウィンドウが表示されます。

ここでテキストとして、クエリテキストに含まれる文言を入力して、虫眼鏡アイコンをクリックすることで、クエリ ID やクエリテキストを検索することができます。

OK をクリックすると、クエリ ID が反映されますので、再生ボタンを押すことで該当のクエリの情報を確認することができます。

クエリのハッシュ値を調べて、テーブルからクエリ ID を確認することもできますが、GUI ベースで検索したい場合はこの流れを覚えておくとよいかと。

■Power BI でクエリストアの情報を可視化

クエリストアの情報はビュー経由でアクセスができ、リレーションについてもかなりシンプルなものになっています。
クエリのストア カタログ ビュー (TRANSACT-SQL)

クエリストアの情報を使用して以下のような Power BI のレポートを作成することができます。

このレポートについては、GitHub で公開しており、Query Store.pbit からダウンロードできるようにしています。
# 2017/2 版の Power BI がインストールされている環境で作成しています。

SQL Server Tiger チームも Power BI でクエリストアの情報を使用したレポートを公開しており、これを参考にしながら作成させていただきました。
Query Performance

Tiger チームが公開している Power BI のレポートは以下のようなものになります。

はじめは、Power BI 側のリレーションシップを使用して、リレーションを設定していたのですが、日付の指定がいくつかの情報に届かなったので、以下のようなクエリで情報を取得するようにしています。

SELECT
	rsi.*,
	rs.*,
	sp.plan_id,
	sp.query_id,
	sp.plan_group_id,
	sp.engine_version,
	sp.compatibility_level,
	sp.query_plan_hash,
	sp.is_online_index_plan,
	sp.is_trivial_plan,
	sp.is_parallel_plan,
	sp.is_forced_plan,
	sp.is_natively_compiled,
	sp.force_failure_count,
	sp.last_force_failure_reason,
	sp.last_force_failure_reason_desc,
	sp.count_compiles,
	sp.initial_compile_start_time,
	sp.last_compile_start_time,
	sp.last_execution_time,
	sp.avg_compile_duration,
	sp.last_compile_duration,
	sq.*,
	qt.*,
	CONVERT(nvarchar(18), sq.query_hash,1) AS query_hash_text,
	CONVERT(nvarchar(18), sp.query_plan_hash, 1) AS query_plan_hash_text
FROM
	sys.query_store_runtime_stats_interval rsi
	INNER JOIN
	sys.query_store_runtime_stats rs
	ON
	rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
	INNER JOIN
	sys.query_store_plan sp
	ON
	sp.plan_id = rs.plan_id
	INNER JOIN
	sys.query_store_query sq
	ON
	sq.query_id = sp.query_id
	INNER JOIN
	sys.query_store_query_text qt
	ON
	qt.query_text_id = sq.query_text_id
WHERE
	start_time >= DATEADD(dd, -10, GETUTCDATE())

クエリストアの情報の蓄積具合によっては、情報取得の負荷が高いことがあるので、直近 10 日のデータに絞って取得するようにしています。

また、データサイズ削減のために実行プランについては取得していません。

# クエリストアの初期設定であれば、100MB 程度のデータ格納になるかと思いますので、データ量的には全件とっても大きな負荷にならないと思いますが。

Power BI 側でパラメーターを使って、何日以前までのデータを取得するかを制御する仕組みにしていたのですが、GitHub に公開するためのデータソースの初期設定が想定した設定にすることができず、今回はクエリに直書きにしています。

# 自社内ツールのようなパブリックにテンプレート公開しないものであれば、パラメーターで日付範囲を指定した作りにはできるかと。

GitHub では、Power BI テンプレートのファイルを公開していますので、ダウンロードをしていただき、以下の操作を実施することで、クエリストアの情報を表示できるかと。

1. Power BI Desktop をインストール
2. GitHub の Query Store.pbit をダウンロードして、Power BI Desktop で開く
3. 「ファイル」→「オプションと設定」→「データ ソース設定」をクリック
   
   
4. 「.;master」を選択して「ソースの変更」をクリック
   
   
5. 確認をしたいサーバー / データベースを指定する
   
   
6. 「OK」をクリックし、認証情報が必要になった場合は認証情報を設定、認証情報の設定については「アクセス許可の編集」からも編集することが可能
   
   

これで、Power BI Desktop からクエリストアの情報を確認することができるかと。

接続情報を SQL Database に向ければ、SQL Database に対しても情報を取得することができます。

Web の Power BI にアップロードして使用することもできますので、SQL Database を使用する場合は、Web にアップロードしてもよいのかなと。

# ゲートウェイを立てれば、オンプレミスに対しても Web 版からアクセスできますが。

Web で使用する場合は、歯車アイコンの「設定」をクリックし、

「データ セット」の「データ ソースの資格情報」の「資格情報を編集」から資格情報を変更すればよいかと。

# SQL Database に接続する場合は、Power BI Desktop で SQL Database に接続をする設定にしてから、Power BI に発行する必要があるかと。