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®.
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?
David – sure, check out the documentation. There’s a great place to request features you’d like to see added to the script. Thanks!