Identifying Missing Indexes in SQL Server

By Samir Behara on August 23, 2017 • ( 2 )

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

view raw

Missing Index DMVs

hosted with ❤ by GitHub

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
	OPTION ( RECOMPILE );

view raw

DMV to find Missing Indexes in your Database

hosted with ❤ by GitHub

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.

Reference –

Tune Your Indexing Strategy with SQL Server DMVs

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