Find Best Clustered Index

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