Categories: SQL Server

Performance Tuning with sp_BlitzIndex

Just a small blog post about how I used sp_BlitzIndex for the first and how awesome that was. sp_BlitzIndex is one of the many free scripts you can get from the great team at Brent Ozar Unlimited.

Recently I went to a client for a one day data warehouse performance tuning exercise. Because you only have one day, it’s important to quickly find the pain-points of the system. I remembered seeing a webinar somewhere of Brent explaining the sp_Blitz script, so I decided to bring those scripts with me. I couldn’t have made a better choice.

There was a serious indexing problem at the client. They had heard “indexes make reads go faster”, so they slapped a lot of indexes on most of the tables. None of them clustered. I ran the script with its default settings and I quickly got a list of all the problems it could find with the indexes on the data warehouse.

It gave me an overview the following items, all of them were immediately actionable:

duplicate indexes. Remove the offenders immediately.
near-duplicate indexes. Check if for example an index has columns (A,B,C) and another index (A,B). Delete the last one.
Heaps. Quite a long list, but the script also has a section on which tables are accessed the most. This allowed us to focus on the more important heaps in the data warehouse.
the so-called work-a-holics: indexes which were used a lot. I focused on making these indexes more efficient: could I make a filtered index out of it? Or maybe add some included columns?

Other topics were listed as well, but these were the main ones I focused on.

What’s great is that this script also provides you with the URLs to knowledge articles on the Brent Ozar website. If you don’t understand one of the results, you can immediately look it up and read about it.

By focusing on the results of sp_BlitzIndex script, I could boost performance in just a few hours of work. This near real-time data warehouse is the source for a reporting application used by dozens of people in the field, and you could immediately tell it worked a lot faster. Awesomesauce.

Disclaimer: I was honestly really impressed with the results. I did not get paid by Brent for this blog post. 🙂

------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂

Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at AE, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.

Next Happy Holidays »

Previous « Master Data Services Configuration Woes

View Comments

Leon Carpay says:

December 10, 2015 at 9:23 am

thanks for sharing Koen. I saw your post on SQL Central and I will definitely dive into Blitz
Lee Markum says:

December 10, 2015 at 1:28 pm

Koen, this is a great summary on sp_BlitzIndex. There are a number of options to the script that let you focus on particular pieces of information about indexes too. I have been using it more and more this year. It really is a fantastic script.
Kenny says:

December 10, 2015 at 11:20 pm

Koen, I have been using different versions of Brent's scripts for a few years now. Such a great way to find problems on a database. I probably use sp_Blitz and sp_BlitzIndex the most although I have tried out many others as well. I like how the scripts prioritize the output so you know which items to start with based on their potential threat level to performance. I recently downloaded the Brent Ozar Unlimited First Responder Kit and it has scripts to troubleshoot just about every part of a SQL server.