The eternal search for missing indexes, part 2

Now that you have tried the script I presented last wednesday on your production server(s) I would like to fill you in on how I use it: The first column in the result set, improvement_measure, shows a weighted measure of several of the other columns in the result set. I usually do not concern myself about values below 1000 for this column. Secondly it is important to group the result into relevant tables. My procedure is to add a filter to the top 1 table in the result set as follows: AND mid.statement LIKE ‘%tablename%’

The result set with this extra WHERE clause will normally contain many similar rows with almost identical CREATE statements in the create_index_statement column. Now you should look at the differences between the statements and find a statement that covers all other statements (normally the most difference is in the INCLUDE part. Now, if your fellow programmer has done som bad programming here, AND you have a many column table, you will find INCLUDE statements that look ridiculous (= every column in the table included). Primary solution is to contact your fellow programmer and in a nice way explain that he (or she) cannot possibly be needing EVERY SINGLE column in the table!

The secondary solution is to make a loud heavy sigh, accept the fact, and try to exclude irrelevant columns yourself. Once you have executed the CREATE INDEX for the chosen index configuration, the DMVs will be reset for the table involved. Therefore you will have to patiently wait until the statistics build up again before you add new indexes to the table. Now you can reiterate the procedure above until you are below your chosen threshold value for the improvement_measure. to be continued…