Let’s Anaylze An Index!

Finally up to the last post of this series resurgence.  Time to update the Index Analysis Custom Report that I developed last year.

The previous posts in this complete 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.
10. Return of Index Analysis Part 3 – Updates to index action column
11. Return of Index Analysis Part 4 – Added a scripting column for CREATE and DROP.
12. Return of Index Analysis Part 5 – Stored procedure wrapper for the index analysis script.

Index Analysis Custom Report

The stored procedure wrapper that was provided in the last post made it pretty easy to access this information from anywhere on your SQL Server instance.  The trouble is, the query results windows isn’t exactly pleasing to the eye. 

To resolve that issue, I opted to create to custom report for SQL Server Management Studio.  A good share of the columns from the index analysis stored procedure are available in the custom report.  The report is grouped by table and has drill-downs into each table to look at the indexes for each table.

This view for the report makes it easy to keep this query on hand and ready for use.  If you find this report useful, please let me know so that I keep updating it. 

Instead of hosting the download on my own site, I have it over on CodePlex.

Viewing A Custom Report

If you haven’t used of custom report in SSMS before, doing so is pretty simple.  First browse to the custom report menu under the database:"

The browse out to the report on your hard drive.  It’s that simple.

Future Updates

I do have a couple other updates for this report in mind.  Ted Krueger (blog | twitter)has a method he suggested that allows the report to be deployed to a Reporting Services instance and then run against any database anywhere on the network.  That update will come soon and be available on the CodePlex site.

Comments and suggestions from other are always welcome as well.

Related posts:

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