asked
Missing index evaluation
I have have to evaluate a set of indices provided by DBA. These were all collected from the DMvs. How can I validate the missing index lists are really valid and it's going to improve any queries? Please suggest me the steps to be followed to evaluate on missing indices Regards, --Rudra
There is no way to correlate the information from the missing index DMVs to a query. That's why I suggest gathering missing index information from the execution plan. I have a very fundamental version of the kind of query you need here:
http://www.scarydba.com/2009/03/02/more-refinements-on-the-missing-indexes-query/
Thanks all for your replies. How do I know which query the missing index is being suggested for? At this moment I am creating statistics and checking for the row density. But this is not complete analysis unless I find the exact query for which the suggested mising index was given for.
This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
Missing Index suggested by DMVs are not always best option. It always depends on your read-write ratio. Get the stats and take decision wisely. Please check this article which will help you to get in detail:
http://www.sqlperformance.com/2013/06/t-sql-queries/missing-index
The recommendation cannot be trusted completely because of few reasons They will not suggest you Missing Clustered Indexes They always comes up with creating new indexes, it never suggest you to modify the existing indexes. It may recommend you to create index on columns that is already exists (Redundant or duplicate Index). You must always analyze the recommendations with the existing index before creating anything new, sometimes you may be good with modify your existing index rather than creating the recommendations.
And indexes require storage which may impact I/O performance. Big topic to initiate correctly.
Just pulling the recommendations from the missing index DMV misses a vital part of the equation, which query needed the index. I usually query directly against the execution plans, which also have missing index information, in order to know which query the missing index is being suggested for. That way, I have a query to test in order to evaluate if the missing index suggestion is worth anything.
17 People are following this question.
