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 sp_BlitzIndex and the First Responder Kit
Want to run sp_BlitzIndex® with minimal permissions? It’s easy, get the prescription here.
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.
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.
If Bad Things Happen to Good Stored Procedures
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.
More Tools for Slow SQL Servers
sp_Blitz®: Free SQL Server Health Check – You’ve inherited a SQL Server from somebody, and you have no idea why it’s slow. sp_Blitz® gives you a prioritized list of health and performance issues, plus gives you URLs for more details about each issue.
Our Free 6-Month DBA Training Plan – Every Wednesday, you get an email with our favorite free SQL Server training resources. We start at backups and work our way up to performance tuning.
SQL Critical Care® – Don’t have time to learn the hard way? We’re here to help with our quick, easy process that gets to the root cause of your database health and performance pains.