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