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.
9 Comments. Leave new
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.
Thanks for catching that! I’ll widen that column definition.
Your fix made sense to me except one of my partition tables was so large I had to make the column nvarchar(max) to avoid the error. Thanks
Thanks! New rev is in the work which handles the partitioning issue– and introducing a new feature or two!
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!
Think I found what I was looking for (https://www.brentozar.com/archive/2012/06/making-spblitz-blitzier/) sorry for asking without doing a deep search. Happy Holidays!
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!
No problem. I’ll ask a person. Thanks for the SP, and like, everything else. This place is amazing.