New Job -> Week 6 -> Ozar sp_BlitzIndex

This is part of a series of new job blog posts.  You can find them all here. 

Week 6 goal: Analyze all Azure SQL Database indexes. Last week, I started pushing out the sp_Blitz scripts that work on Azure.

In the world of SQL Server and Azure SQL, it is essential to have a reliable way to identify performance bottlenecks in your database. Fortunately, Brent Ozar’s sp_BlitzIndex stored procedure is a powerful tool that can help you quickly identify potential problems with your database’s indexes.

The sp_BlitzIndex stored procedure analyzes all indexes in your database and provides a detailed report on their usage and performance. This information can be invaluable when optimizing your database’s performance and improving its overall efficiency.

In this blog post, we’ll look at how to use the sp_BlitzIndex stored procedure.

TL;DR: If you want the code without explanation, visit my GitHub code repository. I use Flyway to deploy these, so they are in the Flyway repository. They live in the Procedures folder and start with R__.

You can get the originals here, but if you want them in a DBA schema as I did, you can use my modified scripts. Not all the Blitz scripts work in Azure, so I’ve picked out the ones that do and will walk through how I’m using them.

Important Bits

Important bits to remember:

• Don’t test in production
• Don’t make too many index changes at once. I like to make one change at a time and let it sit for a bit. See how it helps or doesn’t. Some really great advice from Erik Darling here and my favorite part of the post is what process Erik follows:
  • Get rid of totally unused indexes
  • Come back and see what duplicate indexes are left
  • Merge those together
  • Come back and see what borderline duplicate indexes are left
  • Merge those together
  • Come back and see if there are any indexes with a really bad write-to-read ratio
  • Decide which of those is safe to drop
  • I would add to this adding indexes that are missing

sp_BlitzIndex Basics

Ozar has detailed documentation and even a video on this page on his website. In fact, a few years back, I took this course offered by Ozar on how he uses the First Responder Kit, which includes sp_Blitz stored proc varieties.

To start with, here are the common parameters for sp_BlitzIndex:

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

I generally start with the default mode like so:

EXEC dba.sp_BlitzIndex; 

This provides you with a list of the highest-priority items to research. With the default sp_blitzindex, you get up to priority level 100.

sp_BlitzIndex Default Mode

That previous screenshot is an example of the type of results, but only the first column of those results. Not to worry you get way more info than that, but I can’t show you the exact details of some of it. And the first row is always -1. It gives you the version of sp_BlitzIndex you are using, the server name, and how long it has been up. Here are the included columns:

• Priority – How critical it is to fix this
• Finding – List the issues this index is having
• Database Name – The database name
• Details – Gives you info on the reads/writes using this index along with its name
• Definition – Lists the index columns
• Secret Columns – SQL Server will ensure that all columns from the clustering key of the table are ALSO in the nonclustered index. More info here.
• Usage – Reads/writes on the index
• Size – Size of the index in the number of rows and storage size
• More Info – Script to get more details on the table this index is on
• URL – To give you more info about the finding
• Create TSQL – A script to recreate this index
• Sample Query Plan – This is always NULL in my results

Analyzing sp_BlitzIndex Results

Let’s see how I handle those for which I received results:

• Index Hoader: Unused NC Index with High Writes – I would review what indexes are on the table currently and see if another similar index is in use instead of this one. I would also track this index’s usage over time to ensure it is not in use for reads. If not, I would get rid of it.
• Multiple Index Personalities: Borderline duplicate keys –
  • In the case of the following two indexes, I would most likely drop the first one. The second index covers everything the first index would cover. If they had the exact same columns, I would keep the UNIQUE one. Also, nicely, the second one already has more reads on it, so SQL Server chooses that one over the first one.
    • First index – dbo.tablename.IX_col1_col2
    • Second index – dbo.tablename.UIX_col1_col2_col3 and this second one is UNIQUE
  • In this case, I would keep the ones with the INCLUDES for now with more research into them. This is to see if they are both needed. I can safely drop the first index, though, because it can be covered by the second or third index anyway.
    • First index – dbo.othertable.IX_col1. This has the highest read count, but SQL Server could use the other indexes if this one was unavailable.
    • Second index – dbo.othertable.IX_col1_includes – This includes 11 columns and I would research if that many included columns is really needed.
    • Third index – dbo.othertable.IX_col1_col2_differentincludes – This includes 4 columns that are different from the other index with includes on this table.
  • In this case, I would probably combine these into one index, so I have an index with both columns and then includes with the filter.
    • First index – dbo.yetanothertable.IX_col1_includes where col1 is not null
    • Second index is filtered – dbo.yetanothertable.IX_col1_col2 where col1 is not null
• Indexophobia: High Value Missing Index – I would look at how many indexes are on the table now and if I could modify anything to support this index need. If there aren’t many indexes (like 5 or more) already and nothing else could be easily modified to support this index, I would consider adding it.
• Self Loathing Indexes: Low Fill Factor on Clustered Index – This one makes me feel queasy right away because I never change the fill factor on indexes. It’s usually not recommended. Most likely, this one is being reset to 100.
• Self Loathing Indexes: Small Active Heap – In my case, these are VERY small tables. I will most likely index these anyway because they are active.

There’s even more than what shows up in my results. Ozar has a ton of different types of issues but they aren’t all in my databases. He provides links for each one of them to help you if your results are different than mine.

sp_BlitzIndex Missing Indexes

I also like the missing index mode of sp_BlitzIndex, which you can use by executing the following code:

EXEC dba.sp_BlitzIndex @Mode = 3;

This mode may include indexes recommended in the default mode. The default mode picks up the ones with the highest value, but it’s still worth running in @Mode = 3 to get a complete list. I analyze this list against what indexes I already have. It may be possible to change an existing index to accommodate these recommendations. It’s also important to check how many indexes the table has already. After that research, if I need this index, I will add it.

sp_BlitzIndex Lower Priority Items

Once you get through all the 100 and lower results, you can work through the lower-priority indexing issues by executing:

EXEC dba.sp_BlitzIndex @Mode = 4;

This will return lower-priority indexing issues like the following:

This includes all the same columns as the default mode and includes links to help you solve them if needed.

Conclusion

In conclusion, the sp_BlitzIndex stored procedure is a powerful tool that can help you quickly identify performance bottlenecks in your database. By analyzing the usage and performance of all indexes in your database, sp_BlitzIndex can provide invaluable insights into how to optimize your database’s performance. This will improve its overall efficiency.

Whether you are a seasoned database administrator or just starting out with SQL Server or Azure SQL, sp_BlitzIndex is a must-have tool in your arsenal. So why not give it a try and see how it can help you improve your database performance today?

The post New Job -> Week 6 -> Ozar sp_BlitzIndex appeared first on sqlkitty.