by Martin
24. August 2010 13:49
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}