Starting with SQL Server 2005, Microsoft introduced Dynamic Management Views (DMVs). DMVs return information which can be helpful in diagnosing and correcting performance issues. While there is a lot of information that can be found in the DMVs, this article will focus on identifying missing indexes.
How to Identify Missing Indexes Using SQL Server DMVs
As DBA’s it is not unheard of to hear that “an application running slow” . When a particular query can be identified, the Database Engine Tuning Advisor can be used to look into the query plan. However, many a times the users don’t have the visibility into what is being run. This is where the DMVs can be extremely useful in identifying the most important missing indexes.
The following DMVs contain information on missing indexes. The data returned in the DMVs is collected from the LAST SQL server restart. One should wait for a while after the SQL restart before analyzing the data. On a heavily used system, 24 hours should be sufficient in capturing the data needed for analysis.
sys.dm_db_missing_index_groups - Returns information about what missing indexes are contained in a specific missing index group, excluding spatial indexes.
sys.dm_db_missing_index_group_stats - Returns summary information about groups of missing indexes, excluding spatial indexes.
sys.dm_db_missing_index_details - Returns detailed information about missing indexes, excluding spatial indexes.
The following query can be used to identify the TOP 25 missing indexes for all databases. I think it is a good idea to analyze and test these changes in manageable batches in order to determine if you are seeing the expected results. Once tested and implemented, other missing indexes can be worked through.
SELECT TOP 25
[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
, avg_user_impact -- Query cost would reduce by this amount, on average.
, TableName = statement
, [EqualityUsage] = equality_columns
, [InequalityUsage] = inequality_columns
, [Include Columns] = included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;
Below are some sample results from the query. The TableName will be displayed in the format shown. This allows the query to be modified to filter on a particular database if needed.
The table below provides an explanation for each field in the result set. Review the individual DMVs to identify other columns you may find useful for a particular situation.
Once the missing indexes have been identified, the following query using the same DMVs, can be used to generate the CREATE INDEX statements based on the results of the first query. The CREATE statements should be reviewed before implementing in any environment.
SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
+ ISNULL (dm_mid.inequality_columns, '')
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
In conclusion, missing indexes are one of the many items that can contribute towards the poor performance of a SQL Server database. Writing queries to use existing indexes or creating the necessary indexes will help to eliminate user complaints of poor performance. Look for future blogs that will identify other DMVs that can be used to help diagnose performance problems.
For more information click below:
Have TriCore provide comprehensive remote managed service solutions for your Microsoft SQL Server environment