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,
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
— 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
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!