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
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!
Like this:
Like Loading...
Related
SQLZealots 
Appreciaate this blog post
LikeLike