Implemented

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.

FormerMember
FormerMember
Parents
  • 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.

  • I *always* go back to the developer when they use SELECT * to confirm the queries cannot be "tuned down" first.

Comment Children
No Data