index usage DMV Implementation?

  • hi,

    i have executed the missing indexes DMV Query and it output as

    columnname column_usage

    Status_IndEQUALITY

    Foramted_CityEQUALITY

    State_IDEQUALITY

    City_IDINCLUDE

    how can i use this information to create indexes?

    plz help me.

    🙂

  • This means that the server had a query and that query could have benefited from an index that is based on the columns Status_ind, , Formated_City and State_ID. It also recommends that you’ll add the column City_ID as an included column (most chances are that this would make it a covering index). This doesn’t mean that you should automatically create such an index. You should take into consideration other factors such as how many times the query that would benefit from this index ran? How much would the query will be improved? How many data modifications do you have on this table? Can a narrower index (index that is based only on some of those columns) be sufficient?

    If you decide to create an index that is based on the server recommendation remember that the order of the columns in the recommendation is meaningless and that you should decide of the columns’ order according to there selectivity.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • My number one reccommendation is to make sure the index doesn't already exist (almost).

    many times I end up adding or including 1 column to an existing index to cover whatever query generates the missing index counter.

    In the MS Press MCITP books they had a very clever index advantage that would show you how valuable the index would be for select statements. It helps to weigh the impact and compare relative impacts for all the missing indexes.

    user_seeks * avg_total_user_cost * (avg_user_impact *0.01) as 'index advantage'

    at 10-15,000, an index should be considered.

    It works pretty well for me, but I also consider all the same things that Adi mentions about the impact.

    Oh, and I calculate the space the new index or index column would consume on large tables.

    Cheers!!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply