sp_BlitzIndex®

sp_BlitzIndex® – SQL Server’s Index Sanity Test

Your SQL Server indexes may be less sane than you think. Download sp_BlitzIndex® to find out– or scroll on down and watch a video to see how it help you find out:

  • Do you have duplicate indexes wasting your storage and memory?
  • Would you like help to find unused indexes that are bloating your backups?
  • Have wide clustering keys snuck into your schema, inflating your indexes?
  • Are there active heaps lurking in your database, causing strange fragmentation?
  • Is blocking creeping up behind you before you can realize it?

Our free sp_BlitzIndex® stored procedure quickly does a sanity check on your database and diagnoses your indexes major disorders, then reports back to you. Each disorder has a URL that explains what to look for and how to handle the issue. sp_BlitzIndex® also saves YOUR sanity when index tuning, by giving you the option to see both the “missing” and existing indexes for a table in a single view– when prevents you from going crazy and adding duplicate indexes.

If you’ve ever struggled with index tuning, this may sound crazy good. We think it really is.

Download it in our free First Responder Kit.

Common sp_BlitzIndex® Parameters

@GetAllDatabases = 1 – runs index tests across all of the databases on the server instead of just your current database context. If you’ve got more than 50 databases on the server, this only works if you also pass in @BringThePain = 1, because it’s gonna be slow. @DatabaseName, @SchemaName, @TableName – if you only want to examine indexes on a particular table, fill all three of these out. @SkipPartitions = 1 – goes faster on databases with large numbers of partitions, like over 500. @Mode – options are:

  • 0 (default) – basic diagnostics of urgent issues
  • 1 – summarize database metrics
  • 2 – index usage detail only
  • 3 – missing indexes only
  • 4 – in-depth diagnostics, including low-priority issues and small objects

@Filter – only works in @Mode = 0. Options are:

  • 0 (default) – no filter
  • 1 – no low-usage warnings for objects with 0 reads
  • 2 – only warn about objects over 500MB

@ThresholdMB = 250 – number of megabytes that an object must be before we display its data in @Mode = 0. @Help = 1 – explains the rest of sp_BlitzIndex’s parameters.

Getting support for sp_BlitzIndex®

sp_BlitzIndex® works on SQL Server 2008 and newer. If Microsoft supports it, so do we – unfortunately, that means SQL 2000 and 2005 are out of luck. We use a lot of cool DMVs and techniques that only work in current versions of SQL Server. If sp_BlitzIndex doesn’t compile or if it throws an error when it runs, head on over to DBA.StackExchange.com and post a question. Include the version of sp_BlitzIndex®, your SQL Server version number (including the build #), and tag it with sp_BlitzIndex®. If you’d like to suggest changes or contribute code, visit the Github repository for the First Responder Kit. You can get more instructions, file issues, and read how to contribute to the community.

Don’t have time to learn the hard way? I’m here to help with a quick, easy process that gets to the root cause of your database health and performance pains. My SQL Critical Care® gets to the root of your SQL Server pains and teaches you how to make your databases faster and more reliable. Learn more, see sample reports, and book a free call with me, Brent.

sp_BlitzIndex® Blog Posts

How to Fix sp_BlitzIndex Aggressive Indexes Warnings

sp_BlitzIndex
13 Comments
sp_BlitzIndex gives your indexes a psychiatrist-style evaluation. It’s a lot like a real psychiatrist: it’s just flagging behaviors, and there’s not necessarily anything wrong with being a hoarder, or a workaholic, or a furry. They’re just behavioral traits. Let your freak flag fly. One of the warnings is “Aggressive Indexes,” which means sys.dm_db_operational_stats reports minutes of…
Read More

Learn to Use sp_Blitz, sp_BlitzCache, sp_BlitzFirst, and sp_BlitzIndex with These Tutorial Videos

There have been so many improvements lately to our First Responder Kit scripts that it was time to update our tutorial videos. Each of these is about ten minutes long and shows how to use ’em: How to Use sp_Blitz for a Health Check How to Use sp_BlitzCache to Find Slow Queries How to Use…
Read More

Where Do Missing Index Requests Come From?

Indexing, sp_BlitzIndex
1 Comment
Be honest, here You don’t care about most indexing problems. Duplicates, Borderline Duplicates, Unused, Wide, Non-aligned Partitions… All you’re here for are the Missing Indexes. Wham, bam, your query finishes in less than a second, ma’am. Take this quarter, go downtown and have a rat gnaw that thing off your face. And I get it.…
Read More