March 6, 2009 at 8:49 am
Hello All, I have an opinion question for people that have experience using and implementing the missing index views.
On the database that I work on (I'm only a Developer, but the lead for SQL performance and indexing on the Dev team), I run a daily report where one of those is "GetMissingIndexes". The result of that report contains around 400 indexes that the dm views calculates as being needed. I chose my top 50 or so, and handed it off to the DBA(s) to get their signoff. They replied that they have issues with the amount of columns in the "Include" statements of the create index scripts.
So...question, in your experiences, what is the confidence rating of these scripts? I know I've seen other posters say, try it out, and see how it works on your system, but does anyone have a number like [3/4 of these scripts we kept in the database], or something like that.
Other question...what are the repercussions to having columns in the "include" part of the script? I understand the birds-eye view of them, but not how they affect the database.
Any help is greatly appreciated. Thank you.
March 6, 2009 at 9:03 am
I my view dm view gives the result based on the database usage. So if you execute some query on adhoc basis for many time, then also it creates entry for that query. (If it requires some extra index.)
So best way is, to try the some of these script and check the performance.(As you have already said in your post). And continue this process until you get the expected performance. (There is no guarantee to get the expected performance). This is the only best way to tune the performance.
And as far as the number of column inside INCLUDE is concern, there is no limit you can have as many as you want from the table, but you have to be careful by checking INSERT, UPDATE frequecy on those column.
Regards,
Nitin
March 6, 2009 at 9:07 am
Thanks Nitin, does it change your response knowing that we don't really run Ad-Hoc queries in our production environment? We have a second database that is refreshed from production 1 day later where most/all the ad hoc queries are performed. Wouldn't that result in [most] of the index suggestions that come up are based on the stored procedures that fire and what they need?
Thanks again,
March 6, 2009 at 9:19 am
gregory.anderson (3/6/2009)
So...question, in your experiences, what is the confidence rating of these scripts?
Fairly low. The logic that goes into producing the missing index entries is a portion of what DTA does. The optimiser doesn't check to see if there's a similar index that just needs one more column adding to it. It doesn't check to see if there's a similar entry in the DMV already. Implement them blindly and you're likely to have lots of near-duplicate indexes.
The optimiser also has a habit of suggesting very very wide covering indexes that may not be required. I've seen it suggest a single column index that had 158 include columns (the table had 160 columns total). Maybe there are cases where doubling the size of the table to speed up one query can be justified, but not in the majority of cases
Other question...what are the repercussions to having columns in the "include" part of the script? I understand the birds-eye view of them, but not how they affect the database.
The columns are included in the leaf level of the index, not in the higher, similar to the way that a clustered index has all of the column in its leaf pages (being the actual data pages)
Two side effects to lots of include columns in lots of indexes.
1) Update cost. When the column is updated, all the indexes have to be changed
2) Space. Storing the columns twice requires double the space
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply