Let’s Anaylze An Index!

We’ve got through the first three parts of this continued series on the Index Analysis query last week.  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

With the upcoming Minnesota SQL Saturday event, I’ve been a bit preoccupied but finally have the next post in this series ready to go.

Scripting Column

A while back when I had been originally working on this query, I had been asked about adding in a column that could be used to script out the query.  The column would be able to provide a CREATE or DELETE statement with just a click of the mouse.

That’s what has been added for this update.  You will find two new columns at the right edge of the column list as ddl_create and ddl_drop.

These two columns will provide a generic script that should work in many circumstances to create of drop the index on that row.  Of course, use your own environment to determine names, file group, fill factor, and etc. for the indexes.  And don’t just run out and start building indexes in production.

So without further ado… the script.

Related posts:

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