Instant Index Insight: How to Use sp_BlitzIndex® (video)

You probably don’t have enough time to dig through DMVs trying to figure out which indexes you should add or drop. Whether you’re a DBA or developer, junior or senior, you’re probably too busy doing your real job to master all the index best practices – and now you don’t have to. In this 30 minute video, Kendra Little introduces you to sp_BlitzIndex®, a free script which you can immediately run to see if your indexes are healthy, or if they are heading towards insanity. Want to try out the tool? Check out sp_BlitzIndex®.

Previous Post
sp_Blitz® v16: Snapshots, Recompiles, ShrinkDB, and More
Next Post
The First Step to the Poor Man’s Runbook

5 Comments. Leave new

  • This video is fantastic and the script is even better, countless hours saved!

  • Help. I’m unable to use sp_blitzindex. I’ve created the stored procedure in the master database on a couple of 2008 r2 servers, but whenever I try running it, it fails.

    This is typical of the messages I see:

    Create temp tables.
    Inserting data into #index_columns
    Inserting data into #index_sanity
    Updating #index_sanity.key_column_names
    Updating #index_sanity.partition_key_column_name
    Updating #index_sanity.key_column_names_with_sort_order
    Updating #index_sanity.include_column_names
    Updating #index_sanity.count_key_columns and count_include_columns
    Inserting data into #index_partition_sanity
    Updating index_sanity_id on #index_partition_sanity
    Inserting data into #index_sanity_size
    Warning: Null value is eliminated by an aggregate or other SET operation.
    Adding UQ index on #index_sanity (object_id,index_id)
    Adding UQ index on #index_partition_sanity (index_sanity_id)
    Inserting data into #missing_indexes
    Inserting data into #foreign_keys
    Updating #index_sanity.referenced_by_foreign_key
    Msg 207, Level 16, State 1, Procedure sp_BlitzIndex, Line 584
    Invalid column name ‘OBJECT_ID’.

    Any idea what’s going on? I reported this error last month to your website, and received an email stating that the error would be added to the bug tracker and looked at asap. I haven’t heard anything since then.

    • Hi Steve,

      This issue happens on case sensitive instances. Previously I was testing with the procedure installed in case sensitive databases and running against other case sensitive databases, but that doesn’t catch all the issues that may happen on a case sensitive instance. I have a fix for this checked in and bundled it in with a few other items, such as basic handling of compressed indexes, xml indexes, columnstore instances, etc. I am now in the final rounds of testing before releasing it.

      Testing now includes case sensitive instances, and I run things through my lab and a few other areas before I release to the public. Planning more test automation for the release after this one to make that process go more quickly.

      I’ll put a note here when the next version comes out– thanks for checking back.

      -Kendra

  • I am looking to gather index information on about 150 DB’s. I have been running sp_BlitzIndex on each one (taking a long time), but it would be great to be able to run it on all DB’s and capture the results. Any ideas?

Menu
{"cart_token":"","hash":"","cart_data":""}