How I Use sp_BlitzIndex To Find Indexing Problems In SQL Server Databases

How I Use sp_BlitzIndex To Find Indexing Problems In SQL Server Databases

---

Video Summary

In this video, I dive into the world of SP Blitz Index, a powerful tool written by Kendra Little that helps database administrators analyze and optimize indexes in SQL Server databases. I share my personal approach to using different modes of SP Blitz Index, focusing on how Mode 2 provides detailed information that can help identify which indexes should be kept, removed, or modified based on their usage patterns and performance metrics. I also discuss the importance of customizing the sorting of results to better align with specific database needs, highlighting examples like heavily read tables versus frequently written-to ones. Additionally, I touch on my skepticism regarding missing index suggestions from SQL Server, explaining why they might not always provide actionable insights and how to use them more effectively in certain scenarios.

Full Transcript

Erik Darling here with Darling Data. And at least for now anyway, right? Who knows what will happen over the course of the day. Maybe Microsoft will buy me for $10 million. In case anyone from Microsoft is watching, these are my terms. $10 million. I want a partner salary at level 69. And I want a matte black employee badge. Those are my terms and conditions. If you find them acceptable, you know where to reach me. Leave a comment here. We can figure out if those are legally binding or not later. So today, well, I guess today, there’s been a bunch of videos today. In this video, we’re going to talk about SP Blitz Index, written by the lovely talented, talented, hyper-ultra-cool Kendra Little, and just some of the parameters that I use to do analysis and get information when I’m working with clients so I can figure out which indexes should stay, should go, and occasionally which ones might need to be created.

So, just because there isn’t a lot of activity in my Stack Overflow database, the results of this are rather unimpressive. for some of the analysis portions. So, you know, there’s Mode 0, which gives you somewhat lighter detail than Mode 4, which really goes in and finds like every single problem that we can find. Mode 0 just tends to find some of the more critical ones that you should probably focus on first for some of your larger tables.

Mode 1 gives you aggregate information about all of the index and heap objects in your database. Mode 2 kind of scripts out all of your index definitions and gives you like a lot of information per index about what’s going on with them. Mode 1. Now, all of these are useful in different ways to figure out which tables to focus on.

One thing that I like to do, and this only works in Mode 2, is to sort the output a little bit differently than we might get it normally. So, in Mode 2, like I said, it scripts out, well, it doesn’t script out like it just spits out a script file of your index definitions. It definitely scripts out your index definitions, but it gives you a lot of other stuff too.

So, this is the one that I’m going to run just to show you the results real quick. And so, this is for the Stack Overflow 2013 database. It’ll tell you the schema, the object name, the index name, the index ID, details of the index.

You know, there’s a lot of stuff in here that is useful. It’ll get a little bit further over. It’ll tell you is where the stuff that I tend to focus on goes.

So, the last time an object was used, how many reads those objects have, how many writes those objects have, the size of the object is another good one, right? Rows, reserved MB, all that good stuff.

You know, this is a great column to look at if you’re trying to figure out, you know, maybe which tables to go after. And then, of course, way over here to the right are two columns that I use quite a bit. You have T-SQL to drop indexes and T-SQL to recreate indexes.

So, if you were to say recommend dropping an index on a table and then at a later date someone said, you know what, turns out there’s this query that runs every three months that really likes that index. You would have the create SQL for that index to get that index back.

So, that’s a useful thing to have. Of course, there’s also the option to disable the index, which you could just, you know, right click and rebuild from the GUI if you’re that kind of DBA. And that would make life a little bit easier.

But here in SP Blitz index land, we have drop T-SQL, not disable T-SQL. So, why do I do this? Why do I order results differently? Well, depending on the database, we might care about different things in here, right?

We might have different objectives for what we want to look at. But looking at indexes just sorted by the number of rows or the size of a table might not lead you to the tables that are most problematic in a database in all circumstances. I think a good example of that is, you know, let’s say you have, you know, a trillion row audit table in your database.

The only thing that ever happens to it is singleton inserts. It’s never a point of contention or a problem for end users. But here you come along looking at these results and you say, oh, my God, we have a trillion row heap.

It’s got to have a clustered index. Got to have a clustered index. Everything’s going to, the world’s going to burn if we don’t have a clustered index. And then you go and try to create that clustered index and fill up the transaction log and the server crashes and burns and no one likes you anymore.

I’ll tell you that sometimes you need to focus on different things. So looking at tables that are heavily read from might be a good place to start looking if you wanted to, you know, maybe figure out if there are any indexes that you should have on that table. Looking at tables that are heavily written to might be a good place to start if you want to figure out which tables you might want to clean up indexes on, right?

Get rid of unused and get rid of unused indexes, merge together overlapping indexes that have, you know, sort of matching key column patterns, things like that. Now merging together like like say you have two indexes that have the same key columns, but then slightly different includes you could you could narrow down like two, three, four or more indexes to one index. Just merging those things all together.

So there are a lot of things that you need to consider when you’re figuring out which tables and indexes you want to go after. There is another mode to this that I’m not including here, and that is missing indexes. And the reason that I’m not including that mode, it’s a number between one and four that is not on this screen, is because the more I work with the missing index feature in SQL Server, the less impressed I am with it.

It’s very where clause centric. So if you have columns that you are joining on or ordering by that do not also appear in the where clause, then SQL Server just sticks them in the includes and that may not be the best choice. It also doesn’t give a lot of thought to column order in the index. Sure, it separates columns into equality and inequality, but the only ordering you get, the only consideration for what order columns are in within those groups is their ordinal position in the table.

So like if you think about a column, you know, like table whatever, column A, B, C, D, E, column A is first ordinal position, column E is a fifth ordinal position, and everything else in between fills in the blanks. SQL Server doesn’t give any thought to selectivity or data distribution or anything like that when it gives you these missing index requests. So they are not always terribly insightful.

The other problem is that there is no deep thought put into the missing index requests. Missing index requests are quick, sort of like pre-optimization opportunities for where SQL Server estimates, you know, those cost percentages which are useless, where SQL Server estimates which operators will be the most costly, and sometimes SQL Server will say, oh, look at this clustered index scan.

It’s going to be 90% of the query cost, right? 90% of the cost within the query is a clustered index scan. Then you run the query and get the actual execution plan, and that clustered index scan takes like 200 milliseconds.

And you’re just not going to save the day very often by solving a 200 millisecond problem in a query that runs for 30, 40 seconds. You see? See what I mean there?

So, the missing indexes can be useful points of correlation. So if you find a query that runs a lot, or let’s say, sorry, let’s say you find a query that runs slowly, but maybe it gets a different execution plan every time it runs because it’s not properly parameterized, or, you know, someone, your app developers don’t strongly type their parameters, or, you know, you’re using the bad kind of dynamic SQL you can catenate things in.

So, you know, you could have a lot of different instances of a query, long-running query that, you know, would, like using a plan, but, you know, you wouldn’t have the sort of overall metrics. So you could use, like, if that long-running query had a missing index request in it, you could use that to sort of, that missing index request, the number of uses for that to kind of correlate, well, how many times would a query have used this? So, missing index requests are useful data points, but they are not like the actual, you know, hand to God, biblical truth of what indexes your database should have in them.

There’s also a lot of things that, a lot of the times when there, like, should be an index where SQL Server doesn’t suggest one. I think my favorite is when you have an eager index spool in a query plan, and that signals that SQL Server is just creating an index for you every time the query runs, but then it throws it away afterwards, and you don’t get a missing index request either in the query plan or in the missing index DMVs when you have an eager index spool.

So there’s a lot of sort of blind spots to the missing index request, so I don’t generally spend too much time with them until I have a set of long-running queries that I want to tackle, and if, by happenstance, there’s a missing index request in them, well, you know, then I might go see how much that missing index request pops up. So, anyway.

The last time I tried to record this video, I got to the very end, and you know what happened? There was, like, 50,000 sirens went by my house, and someone started trying to FaceTime me, and there was a phone ringing. So, I don’t know.

Maybe that was some higher power telling me that that was a bad recording of the video. So, now you have this one. I hope you liked it.

I hope you enjoyed yourselves. I hope you learned something. If you enjoy this sort of content, I would urge you to hit the like and subscribe buttons so you can get more of this content automatically from YouTube. You don’t have to, you know, just hit F5 on my page and refresh it like it’s SBHoo is active.

We’re looking for new videos every five minutes. Jamming it in there. So, yeah. I got, I think, two more.

Blitz Cash and Blitz Lock that I’m going to talk about. And then, yeah, we’re going to wrap up with those two. And then, I don’t know what I’m going to do next.

I haven’t quite decided yet. Still trying to find my inspiration. Maybe I’ll crochet something or something.

I don’t know. Anyway, thank you for watching. Can’t find my, can’t find my mouse cursor.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.