Index Analysis: The Re-Return

Posted by & filed under , , , , , .

Lego ComputerA 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:

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.

  • Kevan Riley

    Jason, great script.

    One gotcha I’ve just encountered – sysname datatype is only valid when in lowercase on case-sensitive or binary collation databases.

    • StrateSQL

      Thanks, Kevan. I just installed a case-sensitive instance of SQL Server to test the scripts on going forward. I’ll get these updated and put out a new release this week.