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.
