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)