Identifying Missing Indexes in SQL Server

Having an effective Database Indexing Strategy is a key to your application performance. When we think about optimizing SQL query performance, generally the first thing which comes to our mind are ‘Indexes’. And rightly so, because Indexes tremendously help in fetching your data faster from SQL Server.

Do you have slow running queries as a result of missing indexes in your tables?

I consider running the Missing Index DMV as an easy win. It is generally a helpful indicator about the indexes which are required by the SQL Server Engine to process the workload faster.

Dynamic management views (DMVs) return server state information and can be used to monitor the health of your database server instance and diagnose problems. It gives great insight about what is going on inside SQL Server.

You can identify missing indexes in your SQL queries by 3 ways primarily —

  • Running the Database Engine Tuning Advisor
  • Executing Missing Index Dynamic Management Views
  • SQL Server Engine prompts missing indexes when you generate Execution Plans in SSMS

There are 3 primary DMVs which provide information about the Missing Indexes in SQL Server —

–Returns detailed information about missing indexes
SELECT * FROM sys.dm_db_missing_index_details
–Returns information about what missing indexes are contained in a specific missing index group
SELECT * FROM sys.dm_db_missing_index_groups
–Returns summary information about groups of missing indexes
SELECT * FROM sys.dm_db_missing_index_group_stats

Permission required to run the Missing Index DMV

You need to have VIEW SERVER STATE permission to be able to execute DMVs.

Query to identify Missing Indexes 

SELECT DISTINCT CONVERT(DECIMAL(18, 2) , user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )) AS [index_advantage] ,
migs.last_user_seek ,
mid.[statement] AS [Database.Schema.Table] ,
mid.equality_columns ,
mid.inequality_columns ,
mid.included_columns ,
migs.unique_compiles ,
migs.user_seeks ,
migs.avg_total_user_cost ,
migs.avg_user_impact ,
OBJECT_NAME(mid.[object_id]) AS [Table Name] ,
p.rows AS [Table Rows]
FROM sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK )
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK ) ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK ) ON mig.index_handle = mid.index_handle
INNER JOIN sys.partitions AS p WITH ( NOLOCK ) ON p.[object_id] = mid.[object_id]
WHERE mid.database_id = DB_ID()
ORDER BY index_advantage DESC

The above query has worked great for me. Execute the query against your database and any time the Index Advantage for a particular index is above 1000, start evaluating whether the particular index will be beneficial or not.

Word of Caution

Remember that like other DMVs, the information stored by the Missing Index DMV gets deleted when the SQL Server is restarted.

Do not blindly create new Indexes in your database based on the results of the Missing Index DMV or the suggestion from Database Engine Tuning Advisor.

The line is a thin one – you need enough indexes to speed up your SELECT queries and at the same time you should not create redundant indexes which could potentially slow down your UPDATE/DELETE operations.

Always keep in mind the flip side of the coin — Having redundant indexes can decrease the performance of your data manipulation queries – INSERT/UPDATE/DELETE. And not to forget the additional disk space that is needed to store them. 


Tune Your Indexing Strategy with SQL Server DMVs


Start a 10-day FREE trial at Pluralsight – Over 5,000 courses available

Pluralsight Logo


Categories: Performance Tuning, SQL Server

Tags: , ,

2 replies

  1. Thanks Samir / regards Manas

    Liked by 1 person


  1. Identifying Unused Indexes in SQL Server – dotnetvibes

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: