Question :
tool available to find missing indexes in mssql 2008 ? can we find the missing index using Database Engine Tuning Advisor
Answer :
SQL Server renders a handful of DVMs that can provide you with this information. See the TechNet reference on About the Missing Indexes Feature.
Mainly, you will be dealing with four DMVs:
- sys.dm_db_missing_index_group_stats
- sys.dm_db_missing_index_groups
- sys.dm_db_missing_index_details
- sys.dm_db_missing_index_columns
Note: This is how you do it. But be very careful when considering “missing indexes”. You could end up with a large amount of indexes and data manipulation will take a hit from that. This does not replace prudent index consideration/creation.
If you are new to this have a look at Brent Ozar/Kendra Little’s script sp_blitzindex.
You get a very nice report on the state of the indexes in your database.
Are there any missing, maybe there are a lot of indexes that are unused, a lot of heaps maybe?
All this is addressed and more in this report.
I use this frequently, copy the result to an excelsheet, make necessary changes, use color coding and add date of change. Then run it again a couple of weeks later and compare and so on. It is an easy way to get in to index tuning.
BlitzIndex (Do not forget the video tutorial)
Do not forget that an unused index is maybe only used once a year and could be very important, dialogue with developers is key to success.