Lista saknade index

Ett ständigt problem när man jobbar med databaser är prestanda. Ofta handlar det om att hitta rätt index, något som inte alltid är så lätt. Nedanstående script kan användas för att hitta saknade index i en databas. Den tar hjälp av tidigare exekverade frågor för att hitta index som kan tänkas saknas. Ju högre siffra i kolumnen Score (längst till höger) desto viktigare index. Upp till 100 är oftast ingen större fara. För att få köra koden krävs att man har rättigheten _VIEW SERVER STATE_ Koden är ursprungligen funnen på [url:SQL Server Central|http://www.sqlservercentral.com/scripts/Index+Management/63937/] men har sedan modifierats lite för läsbarhetens skull. {code:sql} DECLARE @SchemeName sysname , @TableName sysname , @Sort Tinyint , @Delimiter VarChar(1) , @crlf char(2) SELECT @SchemeName = '' , @TableName = '' , @Sort = 1 , @Delimiter = ',' , @crlf = char(13) + char(10) SELECT sch.schema_id , 'schema_name' = sch.name , so.object_id , 'object_name' = so.name , so.type , partitions.Rows , partitions.SizeMB , 'equality_columns' = CASE WHEN @Delimiter =',' THEN mid.equality_columns ELSE REPLACE(mid.equality_columns, ',', @Delimiter) END , 'inequality_columns' = CASE WHEN @Delimiter = ',' THEN mid.inequality_columns ELSE REPLACE(mid.inequality_columns, ',', @Delimiter) END , 'included_columns' = CASE WHEN @Delimiter = ',' THEN mid.included_columns ELSE REPLACE(mid.included_columns, ',', @Delimiter) END , migs.unique_compiles , migs.user_seeks , migs.user_scans , migs.avg_total_user_cost , migs.avg_user_impact , migs.last_user_seek , migs.last_user_scan , migs.system_seeks , migs.system_scans , migs.avg_total_system_cost , migs.avg_system_impact , migs.last_system_seek , migs.last_system_scan , 'Score' = (CONVERT(Numeric(19, 6), migs.user_seeks) + CONVERT(Numeric(19, 6), migs.unique_compiles)) * CONVERT(Numeric(19, 6), migs.avg_total_user_cost) * CONVERT(Numeric(19, 6), migs.avg_user_impact / 100.0) FROM sys.objects so JOIN ( SELECT object_id , 'Rows' = SUM(CASE WHEN index_id BETWEEN 0 AND 1 THEN row_count ELSE 0 END) , 'SizeMB' = CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count)) / CONVERT(numeric(19, 3), 128)) FROM sys.dm_db_partition_stats ddps WHERE ddps.index_id BETWEEN 0 AND 1 --0=Heap; 1=Clustered; only 1 per table GROUP BY object_id ) AS partitions ON so.object_id = partitions.object_id JOIN sys.schemas sch ON so.schema_id = sch.schema_id JOIN sys.dm_db_missing_index_details mid ON so.object_id = mid.object_id JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle WHERE mid.database_id = DB_ID() AND sch.name LIKE CASE WHEN @SchemeName = '' THEN sch.name ELSE @SchemeName END AND so.name LIKE CASE WHEN @TableName = '' THEN so.name ELSE @TableName END ORDER BY CASE @Sort WHEN 1 THEN (CONVERT(Numeric(19, 6), migs.user_seeks) + CONVERT(Numeric(19, 6), migs.unique_compiles)) * CONVERT(Numeric(19, 6), migs.avg_total_user_cost) * CONVERT(Numeric(19, 6), migs.avg_user_impact / 100.0) * -1 WHEN 2 THEN (CONVERT(Numeric(19, 6), migs.user_seeks) + CONVERT(Numeric(19, 6), migs.unique_compiles)) * CONVERT(Numeric(19, 6), migs.avg_total_user_cost) * CONVERT(Numeric(19, 6), migs.avg_user_impact / 100.0) ELSE NULL END, CASE @Sort WHEN 3 THEN sch.name WHEN 4 THEN sch.name WHEN 5 THEN sch.name ELSE NULL END, CASE @Sort WHEN 1 THEN migs.user_seeks * -1 WHEN 2 THEN migs.user_seeks END, CASE @Sort WHEN 3 THEN so.name WHEN 4 THEN so.name WHEN 5 THEN so.name ELSE NULL END, CASE @Sort WHEN 1 THEN migs.avg_total_user_cost * -1 WHEN 2 THEN migs.avg_total_user_cost WHEN 4 THEN (CONVERT(Numeric(19, 6), migs.user_seeks)+CONVERT(Numeric(19, 6), migs.unique_compiles)) * CONVERT(Numeric(19, 6), migs.avg_total_user_cost) * CONVERT(Numeric(19, 6), migs.avg_user_impact / 100.0) * -1 WHEN 5 THEN (CONVERT(Numeric(19, 6), migs.user_seeks)+CONVERT(Numeric(19, 6), migs.unique_compiles)) * CONVERT(Numeric(19, 6), migs.avg_total_user_cost) * CONVERT(Numeric(19, 6), migs.avg_user_impact / 100.0) ELSE NULL END, CASE @Sort WHEN 3 THEN mid.equality_columns ELSE NULL END {code:sql}