The following query will compare the nonclustered indexes vs the clustered index and determine which index would qualify as the best clustered index based upon the DMV statistics.

If a clustered index does not exist on the table, it will also suggest one of the existing nonclustered indexes as the clustered index.

DECLARE @NonClusteredSeekPct float
DECLARE @ClusteredLookupFromNCPct float

-- Define percentage of usage the non clustered should
-- receive over the clustered index
SET @NonClusteredSeekPct = 1.50 -- 150%

-- Define the percentage of all lookups on the clustered index
-- should be executed by this non clustered index
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
--AND c.database_id = @DBID
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')
-- non clustered user seeks outweigh clustered by 150%
AND nc.user_seeks > (c.user_seeks * @NonClusteredSeekPct)
-- nc index usage is primary cause of clustered lookups 80%
AND nc.user_seeks >= (c.user_lookups * @ClusteredLookupFromNCPct)
ORDER BY nc.user_seeks DESC

The way it performs this determination is by comparing the lookups for a particular nonclustered index to the current number of seeks for the current clustered index.

• sql server user_seeks
• how to find clustered index
• user_seeks
• user_seeks in sql server
• sql server best index for lookup