SQL clustered index review based on usage

A useful script to see which index the system thinks should be the clustered index, based on query history. This is just providing information as part of a performance review.

DECLARE @NonClusteredSeekPct float
DECLARE @ClusteredLookupFromNCPct float
SET @NonClusteredSeekPct = 1.50 — 150%
SET @ClusteredLookupFromNCPct = .75 — 75%
SELECT
TableName = object_name(idx.object_id)
,NonUsefulClusteredIndex = idx.NAME
,ShouldBeClustered = nc.NonClusteredName
,Clustered_User_Seeks = c.user_seeks
,NonClustered_User_Seeks = nc.user_seeks
,Clustered_User_Lookups = c.user_lookups
,DatabaseName = db_name(c.database_id)
FROM sys.indexes idx
LEFT JOIN sys.dm_db_index_usage_stats c
ON idx.object_id = c.object_id
AND idx.index_id = c.index_id
JOIN (
SELECT idx.object_id,nonclusteredname = idx.NAME,ius.user_seeks
FROM sys.indexes idx
JOIN sys.dm_db_index_usage_stats ius
ON idx.object_id = ius.object_id AND idx.index_id = ius.index_id
WHERE idx.type_desc = ‘nonclustered’ AND ius.user_seeks =
(
SELECT MAX(user_seeks) FROM sys.dm_db_index_usage_stats
WHERE object_id = ius.object_id AND type_desc = ‘nonclustered’
)
GROUP BY idx.object_id,idx.NAME,ius.user_seeks
) nc
ON nc.object_id = idx.object_id
WHERE idx.type_desc IN (‘clustered’,’heap’)
AND nc.user_seeks > (c.user_seeks * @NonClusteredSeekPct)
AND nc.user_seeks >= (c.user_lookups * @ClusteredLookupFromNCPct)
and db_name(c.database_id) = ‘<Database name>’
ORDER BY TableName

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s