SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Database のインデックスの利用傾向を確認する

leave a comment

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

 

実行すると以下のような結果を取得することができます。

image

結果はインデックス単位で取得するようにしています。

クラスター化インデックスに対して、user_scans が多い場合にはテーブルスキャン (全件検索) が行われていることになります。

# クラスター化インデックスのスキャン = テーブルスキャンになります。

現状、[SalesOrderDetail] の [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] への user_scans が 1 となっています。

image

この状態で以下のクエリを実行してみます。

SELECT COUNT(*) FROM sales.SalesOrderDetail WITH(INDEX=0)
GO 10

 

このクエリはテーブルスキャンを 10 回実行するものになります。

実行後の結果が以下になりますが、テーブルスキャンを 10 回実行したので、[user_scans] が 10 上昇していることが確認できますね。

image

このクエリを使用することでテーブルスキャンが多いテーブルを確認することができます。

データ件数が多いテーブルに対して、頻繁にテーブルスキャンが行われている場合は I/O コストが高くなります。

そのようなテーブルに対してインデックスを設定することで I/O コストを抑えることができます。

実際の環境ではテーブルスキャンが発生するケースはインデックスが設定されていない項目での JOIN が多いかと思います。

このような場合には、不足しているインデックスの情報が格納される以下の DMV に情報が上がっている可能性が高いです。

これらの DMV から頻繁にスキャンが行われているテーブルについてのインデックスのアドバイスがあるかを見ると効率よく改善ができる可能性があります。

# 実際には、不要なインデックスが入っていることもありますので吟味する必要はありますが。

非クラスター化インデックスに対してのテーブルスキャンは効率的に検索をするために使われていることもありますので、この辺も注意が必要かもしれないですね。

# クラスター化インデックスのみのテーブルで COUNT(*) をおすすめしない理由 というようなケースがありますので。

データ件数が多く、[user_seeks] (インデックスシーク) が多いテーブルにではなく、[user_scans] (インデックス / テーブルスキャン) が多いテーブルに対して、適切なインデックスを付与することで高い処理効率の改善が見込まれますので、実環境でこの辺の情報を定期的にモニタリングしているとインデックス付与の際の参考情報になるかと思います。

Share

Written by Masayuki.Ozawa

12月 13th, 2013 at 12:24 am

Leave a Reply