2016年6月1日にリリースされたSQLServer2016ですが、
まだまだ実運用されてる方は少ないかと思います。
SQLServer2016の新機能には、クエリの高速化、セキュリティの強化、BIツール郡の強化
など様々なトピックがありますが、この記事では
「クエリストア」というクエリの実行履歴を保存する機能を使って、
実行プランの変更履歴を確認してみようと思います。
目次
- クエリストアがあると何が嬉しいの？
- クエリストアを使ってみる
- クエリストアを使ってみた感想
- 参考URL
クエリストアがあると何が嬉しいの？
チューニングや、性能低下時の調査に大活躍します！
良くあるシチュエーションとしては、
毎日実行している処理が、ある日突然遅くなった場合です。
「データも増えてないのに、クエリが遅くなった！」
「再起動してからある処理だけDBのレスポンスが悪くなった！」
そんなとき、実行プランの変更が原因かもしれません。
実行プランは、SQLが実行されたときに、どのようにデータを走査して探していくかの手順のことですが、
クエリストアがあれば過去にどのような実行プランが使われたかもGUIで確認することができます。
クエリストアを使ってみる
クエリストアはDB毎に有効にすることで利用できます。
DBを右クリック⇒プロパティ⇒クエリストア
最初は操作モードが[オフ]になっているので、[読み取り、書き込み]に変更して[OK]
これだけでクエリストアが使えるようになります。
クエリストアはこんな画面です。
確認できる情報は以下の通りです。
- CPU時間
- 実行時間
- クエリ毎の実行回数
- 論理読み取り
- 論理書き込み
- メモリ使用量
- 物理読み取り
- クエリ実行毎の実行プラン
この記事では、
同じクエリが複数回実行される中で、実行プランが変わる事象を再現して、
クエリストア上で確認してみます。
準備
まず、下記のようなテーブルを作成します。
カラム[ColA]が主キーですが、[ColC]の値に極端な偏りをつけます。
[ColC]には、ほとんど1が入っています。2行だけ2,3が入っています。
また、カラム[ColC]にインデックスを張っておきます。
このテーブルに対してColCの値を指定してデータを取得するとき、
取得する値のレコード量によって、以下のように実行プランが変わります。
■ColC=1を取る場合
ほぼ全件取得なのでインデックスを使ってると余計に時間がかかる⇒ColCのインデックスは使わない
■ColC=2を取る場合
1件のみなのでインデックスが良く効く⇒ColCのインデックスを使う
クエリを実行してみる
今回はパラメータクエリを使います。
まずはCol=2を取得するクエリを3回ほど実行します。
EXEC sp_executesql N'SELECT TOP 10000 * FROM TestTable WHERE ColC = @p1',N'@p1 int', @p1 = 2
ColCのインデックスが使われてることも確認できます。
次にCol=1を指定して実行したいのですが、
実行プランのキャッシュが残っているので、一度クリアします。
キャッシュが残っていると、ずっと同じ実行プランを使い続けます。
このキャッシュはサーバの再起動などでも消えてしまうので、これが実行プランが変わる原因になります。
それではCol=1で実行してみます。
EXEC sp_executesql N'SELECT TOP 10000 * FROM TestTable WHERE ColC = @p1', N'@p1 int', @p1 = 1
ほぼ全件取得になるので、[ColC]のインデックスを使わない実行プランが選ばれるはずです。
予想通りですね。
問題は次です。
ここでまたCol=2を取得するクエリを実行してみましょう。
EXEC sp_executesql N'SELECT TOP 10000 * FROM TestTable WHERE ColC = @p1',N'@p1 int', @p1 = 2
1~3回目ではインデックスを使う実行プランが選ばれましたが、
今はCol=1で実行した時のキャッシュが効いているので、
インデックスを使わない実行プランが選ばれてしまいます！
こういった事象は後から気付きにくいですが、クエリストアがあれば一目瞭然です。
実行速度も大幅に遅くなっているのが分かると思います。
クエリストアを使ってみた感想
・いままで確認が難しかった実行プランの履歴を
こうして簡単に確認できるだけでも価値があるように思います。
運用シーンでも活用できる場面は多いのではないでしょうか。
・ただ、クエリプランに保存される情報の中には、
システム内部で自動的に実行されるクエリも含まれるようで、若干見づらいです。
・また、クエリストアでは実行プランの固定が可能です。
実行プランの固定はヒント句でも可能ですが、
クエリの修正をせずにGUIからのみで設定できるのは嬉しいですね。
・個人的に期待していた実行プラン変更などの自動検知は難しそうです。
ただし、クエリストアの情報は全て、DB内部のシステムビューに格納されているので、
そのビューを直接参照することで、好きなようにデータを取得することができます。
参考URL
クエリのストアを使用した、パフォーマンスの監視
https://msdn.microsoft.com/ja-jp/library/dn817826.aspx
SQLServer自習書
https://www.microsoft.com/ja-jp/cloud-platform/products-SQL-Server-2016-Evaluate.aspx