Blog

Only one thing could have dragged me away from the soft glow of the electric leg lamp glowing in the window… sp_BlitzIndex®

It’s one of those weeks when things get nice and slow. Your business users and managers are all out of the office due to holidays. Your inbox is blissfully quiet. You get a few moments to step back, make sure everything’s running, and catch up on a few of those things you just never have time to look into.

This was always one of my favorite weeks of the year as a production DBA. This isn’t a week when you want to do anything risky, but it’s the perfect week to learn about how your database servers have been running.

It’s Time to Check Your Index Sanity

Good news— unless you’ve just restarted all your SQL Servers, they’re still caching tons of information about your index performance. Even though it’s a quiet week, now is a great time to check on your indexes– is there something crazy hiding in your schema that you need to devote some time to in the new year?

Our free sp_BlitzIndex® stored procedure is designed to give you insight into your index schema and performance. sp_BlitzIndex® rolls through your database and looks for potential gotchas and issues with your indexes. It reads only metadata (no use of heavy DMVs or anything that needs to scan pages in your data tables themselves), then diagnoses what looks like it may get a little bit crazy in your database— everything from heaps with active deletes to multi-column clustered indexes.

sp_BlitzIndex® version 1.4 is Out

Just in time for the holidays, sp_BlitzIndex® is out with fresh updates.

The biggest new features are that sp_BlitzIndex® now diagnoses “Abnormal Psychology” in your indexes. This diagnosis finds indexes of specific types that require special handling and alerts you to their existence. We let you know if we find indexes using page or row compression, or columnstore, XML, or spatial indexes.

sp_BlitzIndex® also includes a few fixes for bugs users reported after our “Instant Index Insight” webcast. The stored procedure now works no matter what the default collation is of your SQL Server instance, and we added a few lines of code that prevents problems if you’re using any default user settings that might produce a problem.

What if You Find Something?

If you find that your indexes are hiding legions of problems, don’t panic. This isn’t the week to panic– this is the week to observe, learn, and plan. Each diagnosis has a URL so that you can learn more about the diagnosis. Spend some time with us and dig into the issue, then plan to make improvements in 2013.

How to Get Started

To spend your week getting to know your database schema and index performance better, get sp_BlitzIndex® from our download page.

↑ Back to top
  1. Hello,

    thanks for this christmas gift.
    To return the favor, I wanted to share a quick fix :

    The data_compression_desc of the table #index_sanity_size (line 283) is not wide enough when you have a lot of partitions.
    I had to pass from varchar(60) to (255) to avoid the error “String data would be truncated”.

    I hope it will make sense for someone.

    Joyeux Noël à tous.

  2. Really useful script, helps me a lot sanitizing our indexes. One question, do you have a .rdl file to display it with a nice format? I can create one, it will be my first time, if there is one, it will save time. Thanks!

  3. Hello. Thanks for this handy (free) script. However, I can’t create the procedure to use it.
    I keep getting:

    Msg 10053, Level 20, State 0, Line 0
    A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 – An established connection was aborted by the software in your host machine.)

    I looked online elsewhere for a reason and couldn’t find any. I have every permission on the server, and I’ve tried on two servers (one 2005 and another 2012) with the same result.

    What could be the issue? I’d just like to use this fancy thing to check if my db design is the bomb or not.

    • Naven – sounds like there’s just connectivity problems between you and the server. Unfortunately, that’s not something we can help troubleshoot quickly in blog post comments. Sorry about that!

  4. No problem. I’ll ask a person. Thanks for the SP, and like, everything else. This place is amazing.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php