Identify Top N Missing Indexes
Applicability:
SQL Server 2000: Not Supported
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Credits:
Author: Unknown/ChillyDBA
Date: 14 Nov 2011/12 Jul 2012
Description
Uses system DMVs to create a weighted list of missing indexes.
The list includes the key columns that could have been used in both equality and inequality predicates plus those that would have been useful as included columns.
Information about how many times Index seek and scan operations would have utilized the indexes is also included.
The weighting is a combination of seeks + scans modified by cost and user impact figures.
The results are only recommendations. Each should be validated against application need and SQL Server resource usage (plus a pinch of common sense), as sometimes the recommendations can produce multiple near-identical recommendations.
Code
Function:
DROP FUNCTION dbo.udf_GetTopNMissingIndexes
GO
CREATE FUNCTION dbo.udf_GetTopNMissingIndexes
(
@n INT
)
RETURNS @TopNMissingIndexes TABLE
(
DatabaseName SYSNAME NULL,
TableName SYSNAME NULL,
EqualityColumns VARCHAR(1000),
InequalityColumns VARCHAR(1000),
IncludedColumns VARCHAR(1000),
UserSeeks INT,
UserScans INT,
LastUserSeek DATETIME,
AvgUserImpact DECIMAL(18,2),
AvgTotalUserCost DECIMAL(18,2),
WeightedCost DECIMAL(18,2),
IndexHandle INT
)
AS
/***************************************************************
Purpose: To retrieve the top N missing indexes.
These are stats created by the DBMS when queries are parsed and indexes are
considered to be a more optimal cost solution but which are missing
NOTE: These are system-generated recommendations and should not be implmented
without careful consideration
The following DMVs are used:
sys.dm_db_missing_index_group_stats - summary missing index information (#usage and cost)
sys.dm_db_missing_index_groups - link between summary and detail
sys.dm_db_missing_index_details - detailed missing index information (columns)
Author: Unknown
History: 14 Nov 2011 - Initial Issue
12 Jul 2012 - ChillyDBA - Converted to a table valued function
with optional number or results (@N)
****************************************************************/
BEGIN
INSERT @TopNMissingIndexes
(
DatabaseName,
TableName,
EqualityColumns,
InequalityColumns,
IncludedColumns,
UserSeeks,
UserScans,
LastUserSeek,
AvgUserImpact,
AvgTotalUserCost,
WeightedCost,
IndexHandle
)
SELECT TOP(@n)
SUBSTRING(mid.statement, 1, CHARINDEX('.', mid.statement) - 1) AS DatabaseName,
SUBSTRING(mid.statement, CHARINDEX('.', mid.statement), DATALENGTH(mid.Statement)) AS TableName,
mid.equality_columns AS EqualityColumns,
mid.inequality_columns AS InequalityColumns,
mid.included_columns AS IncludedColumns,
migs.user_seeks AS UserSeeks,
migs.user_scans AS UserScans,
migs.last_user_seek AS LastUserSeek,
migs.avg_user_impact AS AvgUserImpact,
avg_total_user_cost AS AvgTotalUserCost,
avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) AS WeightedCost,
mid.Index_Handle AS IndexHandle
FROM
sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON (migs.group_handle = mig.index_group_handle)
INNER JOIN sys.dm_db_missing_index_details AS mid
ON (mig.index_handle = mid.index_handle)
ORDER BY
avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) DESC ;
RETURN
END
Test Code:
SELECT * FROM dbo.udf_GetTopNMissingIndexes (20)
SELECT * FROM dbo.udf_GetTopNMissingIndexes (50)