Missing Index Script with Create Statement

Question

Missing Index Script with Create Statement

AndrewL65

Mr or Mrs. 500

Points: 526
More actions
July 11, 2016 at 9:15 pm

#319890

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

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

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

amd.repetto Ten Centuries Points: 1300 More actions · Answer 1

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: 29472 More actions · Answer 2

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 More actions · Answer 3

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