Find Best Clustered Index
-
Posted on November 16, 2009 by Derek Dieter
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. [cc lang=”sql”] 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 [/cc] The way it performs this determination is […]
Continue reading ...
