Let’s Anaylze An Index!

Time for part three of my continued Index Analysis query.  The previous posts in this series are:

1. Analyze Your Indexes Part 1 – Combine existing index statistics and missing indexes into a single output
2. Analyze Your Indexes Part 2 – Find existing duplicate and overlapping indexes
3. Analyze Your Indexes Part 3 – Find the relationship between indexes and foreign keys
4. Analyze Your Indexes Part 4 – Add size and current memory utilization
5. Analyze Your Indexes Part 5 – Add in index analysis guidelines
6. Analyze Your Indexes Part 6 – Add in blocking statistics
7. Analyzing Your Indexes with a Custom Report
8. Return of Index Analysis Part 1 – Add in data from sys.dm_db_index_operational stats.
9. Return of Index Analysis Part 2 – Addition of the pro and con columns.

Query Changes

For this post the plan is to make a few tweaks to the query to improve some of the results.  These changes should improve the results of the index analysis query:

1. Changed the results to only mark the 2nd, 3rd, 4th… etc. indexes in duplicate index occurrences.  The first occurrence of the duplicate is not marked.
2. Any index marked as a duplicate index is marked as “DROP” for index action.
3. Indexes that overlap foreign keys and are not duplicate indexes are exempt from a “DROP” index action.
4. Missing index recommendations that would account for less that 0.01% of the estimated user total percent of usage compared to other indexes on a table are removed from the output.  This removes a lot of noise from the query results.

Please feel free to comment on these changes or any other changes you’d like to see to improve the results.  I’m definitely interested to see if others find this useful.

Index Analysis Query

The last few posts on this query have been a bit lengthy.  So instead the query and column definitions will be accessible through the link below:

Index Analysis Query v0.10

The disclaimers that are usually at the end of the posts has also been moved into the script.  They haven’t gone away, so please read the included documentation.

Related posts:

1. Return of Index Analysis Part 1
2. Return of Index Analysis Part 2
3. Return of Index Analysis