Index Analysis Update – January 2012

Lego ComputerIn the past couple weeks, I’ve gotten a couple reports of issues with sp_indexanalysis.  The update today fixes those issues.  It’s fun to finally be playing with these scripts again.  I’m hoping to start writing blog posts showing the scripts in action in the next couple weeks.


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

  • Fixed issue with overlapping indexes and duplicate indexes related
    filtered indexes
  • Improved error handling to solve issue with truncation message


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

  • SQL Server 2005 – [download id=”65″]
  • SQL Server 2008 – [download id=”64″]
  • SQL Server 2012 – [download id=”63″]

index analysis Requests

Are there any features or use cases you are interested in seeing me blog about?  How about for documentation?  Anything that those using this script don’t understand.  Make a comment below and I’ll work on getting a blog post or ten up on that topic.


Let me know if you run into any issues with these scripts, my e-mail address is in the header of all of the procedures.  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.

2 thoughts on “Index Analysis Update – January 2012

  1. The size of the foreign_key_name has to be increased, the name can be 128 characters and you are adding to it. It failed for me with string truncation. Updated to 256 below and it worked.

    CREATE TABLE #ForeignKeys
    foreign_key_name NVARCHAR(256)
    ,object_id INT
    ,fk_columns NVARCHAR(max)
    ,fk_columns_ids NVARCHAR(1024)



    1. Thanks for finding this. I’ve known about a truncation issue for a bit but wasn’t sure where it was. I’ll get that into the update I have coming next week on the duplicate checks.


Comments are closed.