Index Analysis: The Re-Return

StrateSQL, 2012-12-25 (first published: 2012-12-18)

A couple years back, I wrote a few blog posts about an indexing script that I use with clients from time to time. While I provided some updates to it over the years, I haven’t pushed out anything new on it in quite some time. Seems I got distracted with an indexing book and never got back to pushing out those updates.

Time to fix all that. If you had previously used the script and followed the old blog posts, which have all been tagged with Index Analysis, today’s the day for an update. If you haven’t seen these posts before, take a look. You might find this information interesting and useful. Especially if you need to get a handle on the indexing in your environment.

Index Analysis Script

The basic premise with the index analysis script is to provide a snapshot of the indexes within a database. Depending on the parameters provided, the script can provide results for all of the tables in a database or a single table. By default, the script correlates the missing index and foreign key information into the results to assist in identifying indexes that can be created. Through the index_action column, a brief analysis of the index is performed to assist in identifying issues with the index. The index_pros and index_cons columns provides information on things that are good and bad about the index.

Index Analysis Updates

The main updates to the script in this release are the following:

Rewrote information collection to reduce dynamic SQL
Fixed issue with Object_ID that wasn’t returning correct table
Added unique check and filter definition to duplication and overlapping indexes
Fixed errors with documentation section.
Added parameters @IncludeMissingIndexes, @IncludeMissingFKIndexes, and @Output
Removed DDL create and drop columns for indexes, too much risk in automatic index building
Split the script into three versions for the past three major SQL Server releases

Index Analysis Downloads

Click on the following links to download the index analysis script:

SQL Server 2005 - Index Analysis v9
SQL Server 2008 - Index Analysis v10
SQL Server 2012 - Index Analysis v11

More Information

If you are looking for a few hundred pages of indexing information, you can check out Expert Performance Indexing for SQL Server 2012. Along with that, for indexing resources from my blog, check out the index resource page.