第30回：SQL Serverのインデックス使用回数調査

最近、業務でSQL Serverのインデックス精査を行う必要があり、インデックスがきちんと使用されているのかを確認する目的で使用回数を調査したので、調査方法をご紹介します。

新たにテーブルを作成して、DML実行後にインデックスの使用回数がどのように変化するのかを確認していきます。

１．テストテーブルの作成

まず、テスト用テーブルを作成します。

クラスターインデックスと非クラスターインデックスを作成して比較してみます。
次に1件だけレコードを挿入します。

この段階でインデックス使用状況を確認しました。

user_seeks、user_scans、user_updatesの項目を中心に確認していきます。

user_seeks、user_scansともに、挿入の操作では当然、インデックスは使用されないため0回です。
user_updatesはレコードが挿入されたことにより、両方とも1になっています。

２．参照クエリによる検索回数の確認

それでは参照の操作を何パターンか実行してみましょう。
まず、以下のクエリを実行します。

インデックス使用状況の結果は以下の通りでした。

クラスターインデックスのuser_seeksが1になっているので、Bツリーインデックスをたどって検索したようです。
（なんとなく、1件しかないので全件検索でuser_scansが1になると思ったのですが、そうでもないみたいですね）

次は以下のクエリを実行します。

上記クエリは存在しないデータを検索しているので、検索結果が0件となります。
おそらく、0件結果となるクエリについても、user_seeksが使用されると予想して、非クラスターインデックスのuser_seeksが3になっているだろうと思いますが、どうでしょうか。

インデックス使用状況の結果は以下の通りでした。

非クラスターインデックスのuser_seeksは1だけで、クラスターインデックスのuser_scansが2になっています。
どういうことでしょうか？
もう少し確認してみることにします。

ということで、以下のクエリを実行します。

インデックス使用状況の結果は以下の通りです。

非クラスターインデックスのindex_seeksが2になったので、上記クエリでは非クラスターインデックスのBツリーをたどって検索したようです。
さらに、以下のクエリを実行します。

インデックス使用状況の結果は以下の通りです。

クラスターインデックスのuser_scansが3になっています。
どうやら検索対象にクラスターインデックスが付与されているカラムが含まれていると、オプティマイザがクラスターインデックスの全件検索が速いと判断しているみたいです。

３．更新クエリによる検索回数の確認

次は更新の操作について、何パターンか確認してみます。
まず、以下のクエリを実行します。

インデックス使用状況の結果は、以下の通りです。

クラスターインデックスのuser_seeksが2になっています。
参照の時と同様、レコードが1件だけでも全件検索とはならないようです。
（もしかしたら、作成したばかりで統計情報がないことでそうなっているのかもしれません）

次に以下のクエリを実行します。

インデックス使用状況の結果は以下の通りです。

非クラスターインデックスのuser_seeksが3になっていました。
今までの傾向から、想定通りだと思います。
また、クラスターインデックスなので当然ですが、更新操作によりデータが変更されたのでuser_updatesが増えています。

それでは次のクエリです。

インデックス使用状況の結果は以下の通りです。

非クラスターインデックスのuser_scansが1になっています。
予想ではクラスターインデックスのuser_scansが増えると思っていたので、これは意外でした。
UPDATE文にWHERE句を付けないということはないと思いますが、ちょっと気を付けないといけないですね。

４．削除クエリによる検索回数の確認

最後に削除操作について何パターンか確認してみましょう。
まず、以下のクエリを実行します。

インデックス使用状況の結果は以下の通りです。

非クラスターインデックスのuser_seeksが4になっているのは想定通りでしたが、削除件数が0件にもかかわらず、両インデックスのuser_updatesが増えています。
どうやらこの列の情報は、操作の結果、実際に更新されたかどうかではなく、あくまで操作が発生したかどうかをカウントしているようですね。

次に以下のクエリを実行してみます。

インデックス使用状況の結果は以下の通りです。

クラスターインデックスのuser_seeksが3になっているので想定通りです。

次はこちらです。

インデックス使用状況の結果は以下の通りです。

クラスターインデックスのuser_scansが5になっているので、想定通りです。

最後は以下のクエリを実行してみます。

インデックス使用状況の結果は以下の通りです。

更新操作の時と同様、非クラスターインデックスのuser_scansが増えています。
DELETE文の時も、WHERE句がないと非効率となりそうです。

５．おわりに

今回は作成したばかりでレコード数1件、統計情報なしという状態だったので、必ずしも今回のような動作にはなりませんが、なかなか興味深い結果となりました。

当たり前ですが、UPDATEやDELETE文には必ずWHERE句を付けましょう！

以上です、最後までお読みいただき、ありがとうございました。