Implemented
over 1 year ago

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.

  • I agree, I voted this down too.  I'd prefer to look at the execution plan and the query to see if I need to add an index, or alternatively refactor the query.

  • I don't understand the reluctance for some to receive index recommendations.  As stated previously, I would rather have the information and determine whether or not to add the index.  While it is possible to add too many indexes to a table, indexing is the key to performance (no pun intended).  As a DBA working on performance issues, your options are to; 1 - rewrite the query, which is sometimes your best option if possible, 2 - change the structure of the database, i.e. de-normalize, etc., or 3 - add an index.

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

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