When you’re tuning queries It’s sort of a relief when the first time you get your hands on it, you get the plan and there’s a missing index request. Even if it’s not a super high-value one, something in there is crying for help. Where there’s smoke, there’s a bingo parlor. But does adding missing…
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
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.
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. 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. Our 3-day 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 us.