cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Index Analysis (including Missing Indexes)

Index Analysis (including Missing Indexes)

Since databasses report missing index data, it would be nice to see this in DPA so we can associate the largest wait time sql with missing index data.

Provide analysis about current indexes on a table

Show unused or underused indexes.

10/12/16 - changed header and description to reflect the feature better

This feature has been implemented in DPA 12.0 and is now available for download in the customer portal

See these posts for more information about DPA 12.0.

18 Comments
Level 8

I voted this feature down as I find too many people just want to add indexes to fix any performance issue as a 1st step.  Indexes are not free and are very hard to get removed once they make their way onto production.  Obviously they are sometimes necessary, however, it isn't hard to figure out if an index would be helpful once you understand what the execution plan should be doing.  So it isn't that this wouldn't be a helpful feature, but I feel it will lead to too many indexes getting added to production systems without proper understanding if that is actually a good idea.

Level 12

You've got an excellent point cmartin2.  Blindly adding suggested indexes is not a good idea.  Thanks for contributing!  Hope to see you around on THWACH!  🙂

Level 8

I also voted this down  for the same reason as cmartin2. There are too many variables that go into when to create, modify or drop an index.

Level 12

While I agree with @cmartin2 that indexes are not free, I still think it would be useful to see an indication of a missing index as part of the diagnostics.  It's not compulsory to add the index, but it may aid in the analysis of the problem.

Level 12

The page that displays these could have a statement to that effect. But the fact that the information can be misused is not a reason to hide it from those of us that know how to evaluate it and take only appropriate action. The ability to mark a specific missing index recommendation so it will be hidden from view once it has been evaluated and rejected would make this even more helpful. Maybe even to allow the user to enter a comment as to why it is being rejected, and offer a selection on the page to show all recommendations, hidden/rejected recommendations, or non-hidden (the default).

Level 7

i agree with gcp, it would help.

I would vote YES for this... but it depends on how it is implemented. We have a weekly process now that captures all of the recommended missing indexes (as well as "unused" indexes), which we aggregate to review which indexes get the highest "impact/occurrences".

As with any SQL modification, a good knowledge of the data and queries used definitely goes a long way! Just because you can does not mean you should! 

Level 12

Simply surfacing the missing index recommendations from an execution plan, or all of them from the plan cache, would be helpful to some people. What would be even better would be to add some value to them, especially in the context of an Advisor.

A missing index recommendation may really indicate a need to just to add a key or include column to an existing index. There can also be a case where there is no existing index that is at all reasonably close, but two missing index recommendations may be similar to each other and should probably be combined, differing only by an extra key column (but the first ones are all the same order) or by a difference in include columns.

It will sometimes recommend indexes with one key column, and then include every other column because someone is running SELECT *, or the equivalent in a LINQ statement, because they don't take the time to pick the columns they actually need. In these cases, finding and modifying the queries is probably a better idea than adding the index, especially if the query has only been executed a small number of times.

Ranking the recommendations in some way (perhaps a documented default sort on the results) would help keep people from spending time on indexe changes that would be insignificant to the overall performance of their database. But that gets tricky. How should these be ranked relative to each other? Maybe we need to consider all of these.

  • An index that would improve an infrequently executed query with a high-cost plan by 95%
  • An index that would improve a frequently executed query with a low-cost plan by 95%
  • An index that would improve a frequently executed query with a high-cost plan by 20%

There are also cases where a plan will have an expensive key lookup that could be fixed by adding a single include column to the index already being used by the query, yet Microsoft provides no missing index notification. I know a lot of people that work with SQL extensively who have never heard of the INCLUDE clause. While dealing with this may be beyond the scope of the feature being suggested here, it does provide another way in which DPA could add value in this area.

Level 17

Great line of thought!

#bumpsquad

Level 8

I would be satisfied if DPA would Display if there are fragmented Indexes of the longgest running sqls