Missing Index Script with Create Statement

  • AndrewL65

    Mr or Mrs. 500

    Points: 526

    Comments posted to this topic are about the item Missing Index Script with Create Statement

  • amd.repetto

    Ten Centuries

    Points: 1035

    Hi, it looks a very good and useful script 🙂 I'm just wondering on which columns should I base my decition to create or not the index. It would be really helpful if you can add a brief description on each column that you are showing, in that way it will make the article better for newbies like me.

    Thanks!

    A. Mauricio Repetto
    ML Engineer

  • SQL Galaxy

    One Orange Chip

    Points: 29255

    Hi Andrew LAckenby.

    Your script is very useful for finding missing index details in database. I executed one of the busy OLTP database and outputs are 45 create index statements in single table and retrieved by script.

    could Pls. explain the following outputs

    index_advantage = 7983.73805718724

    user_seeks = 116

    avg_total_user_cost = 92.4947293095018

    avg_user_impact = 74.41

    Thanks

  • AndrewL65

    Mr or Mrs. 500

    Points: 526

    SQL Galaxy (9/23/2016)


    could Pls. explain the following outputs

    index_advantage = 7983.73805718724

    user_seeks = 116

    avg_total_user_cost = 92.4947293095018

    avg_user_impact = 74.41

    Hi there,

    index_advantage = This is a calculated column. Indexes with a higher index_advantage are those that SQL Server considers will have the biggest positive impact on reducing workload, based on reducing query cost and the projected number of times they will use the index.

    user_seeks = Number of seeks caused by user queries that the recommended index in the group could have been used for.

    avg_total_user_cost = Average cost of the user queries that could be reduced by the index in the group.

    avg_user_impact = Average percentage benefit that user queries could experience if this missing index group was implemented. The value means that the query cost would on average drop by this percentage if this missing index group was implemented.

    Bear in mind that if you make a change to an index for a given table, then all the missing index statistics for that table are cleared out and repopulated again over time. If you run this query shortly after an index change, it will probably—and inaccurately—inform you that there are no missing indexes for this table.

    You should never just blindly add every index that this query suggests, especially if you have an online transaction processing (OLTP) workload. Instead, you need to examine the results of the query carefully and manually filter out results that are not part of your regular workload.

    Regards

    Andrew Lackenby

Viewing 4 posts - 1 through 4 (of 4 total)

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