SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Return of Index Analysis Part 4

Looking for Minifig Monday?
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


Posted by Anonymous on 22 October 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, Return of Index Analysis Part 4 - StrateSQL         [sqlservercentral.com]        on Topsy.com

Leave a Comment

Please register or log in to leave a comment.