More than 5 years have passed since last update.

【SQLServer2016新機能】クエリストアで実行プランの変化を監視する

Last updated at 2017-03-16Posted at 2017-03-16

2016年6月1日にリリースされたSQLServer2016ですが、
まだまだ実運用されてる方は少ないかと思います。

SQLServer2016の新機能には、クエリの高速化、セキュリティの強化、BIツール郡の強化
など様々なトピックがありますが、この記事では
**「クエリストア」**というクエリの実行履歴を保存する機能を使って、
実行プランの変更履歴を確認してみようと思います。

# 目次

# クエリストアがあると何が嬉しいの？

チューニングや、性能低下時の調査に大活躍します！
良くあるシチュエーションとしては、
毎日実行している処理が、ある日突然遅くなった場合です。

「データも増えてないのに、クエリが遅くなった！」
「再起動してからある処理だけDBのレスポンスが悪くなった！」
そんなとき、実行プランの変更が原因かもしれません。

実行プランは、SQLが実行されたときに、どのようにデータを走査して探していくかの手順のことですが、
クエリストアがあれば過去にどのような実行プランが使われたかもGUIで確認することができます。

クエリストアを使ってみる

クエリストアはDB毎に有効にすることで利用できます。
DBを右クリック⇒プロパティ⇒クエリストア

最初は操作モードが[オフ]になっているので、[読み取り、書き込み]に変更して[OK]
これだけでクエリストアが使えるようになります。

クエリストアはこんな画面です。

確認できる情報は以下の通りです。

この記事では、
同じクエリが複数回実行される中で、実行プランが変わる事象を再現して、
クエリストア上で確認してみます。

まず、下記のようなテーブルを作成します。
カラム[ColA]が主キーですが、[ColC]の値に極端な偏りをつけます。
[ColC]には、ほとんど1が入っています。2行だけ2,3が入っています。

また、カラム[ColC]にインデックスを張っておきます。

このテーブルに対してColCの値を指定してデータを取得するとき、
取得する値のレコード量によって、以下のように実行プランが変わります。

■ColC=1を取る場合
ほぼ全件取得なのでインデックスを使ってると余計に時間がかかる⇒ColCのインデックスは使わない

■ColC=2を取る場合
1件のみなのでインデックスが良く効く⇒ColCのインデックスを使う

今回はパラメータクエリを使います。

まずはCol=2を取得するクエリを3回ほど実行します。

test.sql

EXEC sp_executesql N'SELECT TOP 10000 * FROM TestTable WHERE ColC = @p1',N'@p1 int', @p1 = 2

ColCのインデックスが使われてることも確認できます。

次にCol=1を指定して実行したいのですが、
実行プランのキャッシュが残っているので、一度クリアします。
キャッシュが残っていると、ずっと同じ実行プランを使い続けます。
このキャッシュはサーバの再起動などでも消えてしまうので、これが実行プランが変わる原因になります。

それではCol=1で実行してみます。

test.sql

EXEC sp_executesql N'SELECT TOP 10000 * FROM TestTable WHERE ColC = @p1', N'@p1 int', @p1 = 1

ほぼ全件取得になるので、[ColC]のインデックスを使わない実行プランが選ばれるはずです。

予想通りですね。

問題は次です。
ここでまたCol=2を取得するクエリを実行してみましょう。

test.sql

EXEC sp_executesql N'SELECT TOP 10000 * FROM TestTable WHERE ColC = @p1',N'@p1 int', @p1 = 2

1~3回目ではインデックスを使う実行プランが選ばれましたが、
今はCol=1で実行した時のキャッシュが効いているので、
インデックスを使わない実行プランが選ばれてしまいます！

こういった事象は後から気付きにくいですが、クエリストアがあれば一目瞭然です。

実行速度も大幅に遅くなっているのが分かると思います。

・いままで確認が難しかった実行プランの履歴を
　こうして簡単に確認できるだけでも価値があるように思います。
　運用シーンでも活用できる場面は多いのではないでしょうか。

・ただ、クエリプランに保存される情報の中には、
　システム内部で自動的に実行されるクエリも含まれるようで、若干見づらいです。

・また、クエリストアでは実行プランの固定が可能です。
　実行プランの固定はヒント句でも可能ですが、
　クエリの修正をせずにGUIからのみで設定できるのは嬉しいですね。

・個人的に期待していた実行プラン変更などの自動検知は難しそうです。
　ただし、クエリストアの情報は全て、DB内部のシステムビューに格納されているので、
　そのビューを直接参照することで、好きなようにデータを取得することができます。

クエリのストアを使用した、パフォーマンスの監視
https://msdn.microsoft.com/ja-jp/library/dn817826.aspx