Return of Index Analysis Part 5

By StrateSQL, 2010/10/25

: Let’s Anaylze An Index!

Alright, up to the last of the changes I’ve made to the index analysis query that I had planned. This makes the fifth post in the series on this query.

The previous posts in this complete series are:

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

Stored Procedure Wrapper

A while back I was using the index analysis query at a client, I couldn’t find the most recent version of the query. It can happen when you make a tweak at one client, and then another at a different client. Soon there were a few versions floating around and the one that is the most “correct” is became harder to identify.

To help prevent this, I had re-written the query as a stored procedure. That way it can be dropped into the master database. By being in the master database and having the sp_ prefix, then the procedure can be accessed from any database on the SQL Server instance.

Below are two links that can be used to build the stored procedure. One for SQL Server 2005 and the other for SQL Server 2008/R2.

sp_IndexAnalysis for SQL Server 2005

sp_IndexAnalysis for SQL Server 2008+

If you have any comments or suggestions, please leave them as a comment. I consider this a work in progress to help provide a method to get good information on indexes with very little effort.