SQL Database 向けのクエリとして書いていますが、SQL Server でも使えます。
以前、Community Open day でお話しさせていただいた際にも似たようなことをやりました。
COD2012 T2/T3 : 実機で試す SQL Server の現状取得 ハンズオンマニュアル
COD2012 T2/T3 : 実機で試す SQL Server の現状取得
SQL Database には、以下の二つの DMV があります。
この 2 つの DMV を使用するとデータの件数とインデックスの使用状況を確認することができます。
具体的には以下のようなクエリを実行します。
select SCHEMA_NAME(st.schema_id) as schema_name , OBJECT_NAME(dp.object_id) as object_name , si.name , used_page_count , reserved_page_count , row_count , user_seeks , user_scans , user_lookups , user_updates , last_user_seek , last_user_scan , last_user_lookup from sys.dm_db_partition_stats dp left join sys.indexes si on si.object_id = dp.object_id and si.index_id = dp.index_id inner join sys.tables st on st.object_id = dp.object_id and SCHEMA_NAME(st.schema_id) <> 'sys' left join sys.dm_db_index_usage_stats iu on iu.object_id = dp.object_id and iu.index_id = dp.index_id order by row_count desc ,last_user_scan desc
実行すると以下のような結果を取得することができます。
結果はインデックス単位で取得するようにしています。
クラスター化インデックスに対して、user_scans が多い場合にはテーブルスキャン (全件検索) が行われていることになります。
# クラスター化インデックスのスキャン = テーブルスキャンになります。
現状、[SalesOrderDetail] の [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] への user_scans が 1 となっています。
この状態で以下のクエリを実行してみます。
SELECT COUNT(*) FROM sales.SalesOrderDetail WITH(INDEX=0) GO 10
このクエリはテーブルスキャンを 10 回実行するものになります。
実行後の結果が以下になりますが、テーブルスキャンを 10 回実行したので、[user_scans] が 10 上昇していることが確認できますね。
このクエリを使用することでテーブルスキャンが多いテーブルを確認することができます。
データ件数が多いテーブルに対して、頻繁にテーブルスキャンが行われている場合は I/O コストが高くなります。
そのようなテーブルに対してインデックスを設定することで I/O コストを抑えることができます。
実際の環境ではテーブルスキャンが発生するケースはインデックスが設定されていない項目での JOIN が多いかと思います。
このような場合には、不足しているインデックスの情報が格納される以下の DMV に情報が上がっている可能性が高いです。
これらの DMV から頻繁にスキャンが行われているテーブルについてのインデックスのアドバイスがあるかを見ると効率よく改善ができる可能性があります。
# 実際には、不要なインデックスが入っていることもありますので吟味する必要はありますが。
非クラスター化インデックスに対してのテーブルスキャンは効率的に検索をするために使われていることもありますので、この辺も注意が必要かもしれないですね。
# クラスター化インデックスのみのテーブルで COUNT(*) をおすすめしない理由 というようなケースがありますので。
データ件数が多く、[user_seeks] (インデックスシーク) が多いテーブルにではなく、[user_scans] (インデックス / テーブルスキャン) が多いテーブルに対して、適切なインデックスを付与することで高い処理効率の改善が見込まれますので、実環境でこの辺の情報を定期的にモニタリングしているとインデックス付与の際の参考情報になるかと思います。