How to identify Missing Indexes in SQL Server

Recently, one of my colleagues asked “How do I identify the missing indexes on my databases”?

It is one of the important and very frequent questions in SQL Server. Just think of a book without an index page and how its tough for us to go directly to a desired topic. Likewise Index in SQL Server helps us to fetch the data quickly and improve the performance of your application.

There are 3 ways to identify missing indexes, but you know, I would say none of them are considered as rock solid/fool proof ones. Because, these all three ways can recommend only relatively or estimated ones. It depends on lots of factors like where do you run these tools/ whats the workload of your system etc. Anyway, Let me quickly take you through the 3 ways.

    1. Using Dynamic Management Views:

	Select	OBJECT_SCHEMA_NAME(mid.object_id,mid.database_id) AS Schema_Name,
		db_name(mid.database_id) As Database_Name,
		Object_name(mid.object_id,mid.database_id) As Object_Name,
		migs.avg_user_impact, mid.equality_columns,
		mid.inequality_columns, 
		mid.included_columns
	From sys.dm_db_missing_index_groups mig
	Inner join sys.dm_db_missing_index_group_stats migs 
		on mig.index_group_handle =  migs.group_handle
	Inner join sys.dm_db_missing_index_details mid
		on mig.index_handle = mid.index_handle

Limitations:

— It can lead to multiple recommendations that ends up with duplicate indexes; that means, it does not consider existing indexes.

— It does not even consider existing recommendations for another recommendation as well.

— It is based on individual Query, not on the workload of the system.

— There is a limit of DMV entries per instance, so if you have lots of databases in a single instance, it may not give you the recommendations holistically.

— DMV information will be lost/cleared when SQL Server restarts

— This will not recommend – index type/partitioning/column store/unique constraints etc.

    2. Using Database Tuning adviser

    3. Using Cached plans

This has already been discussed in another blog here.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Leave a Reply

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

WordPress.com Logo

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

Google photo

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

Twitter picture

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

Facebook photo

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

Connecting to %s