Indexing SQL Server Queries For Performance: Blocking and Deadlocking
Thanks for watching!
Video Summary
In this video, I delve into the intricacies of SQL Server indexing for performance optimization, specifically addressing how to index tables effectively while avoiding common pitfalls like over-indexing. I also explore the ins and outs of blocking and deadlocking issues under the default isolation level “read committed,” explaining why enabling read committed snapshot isolation can significantly reduce these problems. Throughout the video, I share practical examples and tips on how to tune both select and modification queries for better performance, emphasizing the importance of considering your isolation level when dealing with indexing challenges.
Full Transcript
Erik Darling here with Darling Data. Feeling ultra professional today for various reasons. Did some cool stuff, some very cool SQL Server stuff, but of course it’s nothing I can talk about here because, you know, it wouldn’t make any sense. In today’s video we are going to continue with talking about how to index SQL Server for performance. And in this video, we’re going to talk about how to index SQL Server for performance. And in this video we’re going to address problems with blocking and deadlocking. Now, what’s funny about this is this is a situation that really rares its ugly head, at least between read queries and write queries, under the unfortunate default isolation level read committed. If you need some help understanding how terrible an isolation level read committed is, you should watch the playlist of isolation level read committed is, you should watch the playlist of videos that I have in my channel called Everything You Know About Isolation Levels Is Wrong, where I will go into great detail about how awful read committed is as a default choice for SQL Server. And I really hope that more people start understanding how lousy and isolation level it is, especially as Microsoft does takes, takes some steps towards saying, hey, maybe you should do something else. For example, you know, in Azure SQL EB, read committed snapshot isolation is the default. You’re allowed to turn it off if you really need to, but you know, if Microsoft is like, you know, hey, this is the default here, maybe it’s not as bad as those stupid blog posts made it out to be that you may have read. And, you know, another thing to consider is that there’s a new feature that’s a new wish feature that’s rolled out to Azure SQL DB, and seems to be full steam ahead with being present in SQL Server 2025, called optimized locking. And if you read the docs page or the learn page, I forget what they start, whatever they call it these days, for optimized locking, there are two suggestions in that page. One of them is to enable accelerated database recovery recovery, actually, I think that one’s a requirement, I forget. And the other one, which is definitely a suggestion, but a strong suggestion, is to enable read committed snapshot isolation, makes it makes the optimized locking work better. So if you are, you know, like, wow, I’d like to optimize some locking my SQL Server database, enabling read committed snapshot isolation is a pretty good way to do that. Anyway, right, you have less blocking between read query and write queries. That’s great. You have less deadlocking between read queries and write queries, all you have to do is turn on this one thing. And your life will be instantly better. But anyway, before we get into all that, let’s talk about how what a bunch of generous souls you are. Up to nearly 40 people who will become members of my channel, and donate some for four or four or more dollars a month to support me making this content. So that’s very sweet of all of you. I appreciate every last one of you. I do not appreciate the 30% that YouTube withholds in taxes, but that’s not your fault.
If you don’t have the $4. If you don’t have the $4, liking, commenting, subscribing, all just, I mean, very humanitarian efforts on your part. I would appreciate that. If you need help with SQL Server, blocking and blocking included, if you would like to hear more about our Lord and Savior read committed snapshot isolation, or even snapshot isolation, you can hire me as a consultant to do these things. And as always, my rates are reasonable. Training. I have stuff that I can teach you, including about locking and blocking. Wow, what a low price, about $150 USD. And you can get all of that stuff at that link up there with that discount code there. And there’s also a link to that in the video description in case you didn’t, there weren’t enough reasons to look at the video description. Now you have this one. It’s wonderful. Again, upcoming events for 2025 will be announced as soon as I hear about them. All sorts of stuff that I got to deal with, isn’t there? All sorts of things, also the hoops I have to jump through before I start announcing things.
But let’s talk about locking and blocking in SQL Server. And what was that? Okay, that was where I rolled that thing back. We’re good there. All right. All right. Let’s make sure we have nothing going on. Now, what I’m going to show you in this video is how, well, you know, it’s the default read committed isolation level, which most of you are suffering under. And before I show you stuff about how adding an index can help fix some blocking and deadlocking problems, I do need to warn you a little bit about how indexes can cause problems.
There are three main areas where over-indexing. And by over-indexing, I don’t necessarily mean the number of indexes you have. I mean, like how those indexes are used and how those indexes are defined. So when I when I think about over-indexing, I think about if you have tables with lots of indexes on or with indexes, don’t even lots, just with indexes on them that have like zero reads and a high number of writes or a really low number of reads and a high number of writes. Or I’m thinking about indexes that have very similar key column orders, maybe different included columns and stuff, but like, you know, indexes that you could theoretically merge together.
So you have like one index that does the work of, you know, two or more indexes. To me, that’s where that’s really over-indexing. If you have a bunch of indexes on a table that all have very different definitions or even different enough definitions, but they’re all doing like hard work, like they’re like queries are reading from them a lot. They’re helping queries go faster. You’re not necessarily over indexed.
But the three main areas where over indexing, as I just described, it can hurt you are one, the buffer pool, right? Because most SQL servers I look at just don’t have enough memory compared to the amount of data they have. So you have all these different indexes competing for space in the buffer pool.
The transaction log, because every time you modify data in the base table, you have to modify data in any indexes where the column that have the columns in them also change. So like for inserts and deletes, that’s, unless you have filtered indexes, that’s going to be like all of them.
For updates, it’s just going to be the columns that are in the index definition, if those changed. And the third area is going to be around locking. The more indexes you have that have to change when data gets put into a table, the more you increase the likelihood of locking and blocking situations and deadlocking situations.
And, of course, you run the chance of SQL Server trying to escalate locks from the row or page level up to the object or table level. So there are ways that over-indexing, having lots of unused indexes, duplicative indexes, stuff like that can hurt you. But you do need indexes to make queries go faster so that you run, like fast queries run less of a risk of having those problems or like being slow and causing problems or other queries, interfering with other queries, stuff like that.
In my experience, unless you’re in Microsoft’s cloud, because Microsoft does this really, plays this really nasty trick on its customers. And it doesn’t matter what you use. It doesn’t matter if it’s Azure SQL DB.
It doesn’t matter if it’s managed instance. It doesn’t matter if it’s a hyperscale. Microsoft throttles your transaction log. So writing that stuff out to the, writing data modifications out to the transaction log can be incredibly slow and painful.
So if that’s your problem, that’s not necessarily the fault of the indexes. That is the fault of your cloud provider. You should, you should maybe have a talk with them about how underhanded of a maneuver that is.
But what was I going to say? Anyway, you can end up with locking and deadlocking problems when you sort of have the opposite problem, right? When you have too few indexes.
Because whenever we, like I talk to people about index tuning, without fail, the majority of the queries that they’re worried about are going to be select queries. Very few people mentally think or like understand that modification queries need indexes too. The faster modification queries can find the data they get to, the better off you are.
Sort of in a general sense, I mean, aside from like query speed, if your modification query locates the data that it needs to change via seek, you will most likely, like unless you’re hitting a lot of rows, start with row locks. If your data modification query acquires the data that it needs to change via a scan, is most likely going to start with page locks.
Again, unless you’re hitting a lot of rows right off the bat. But let’s just take a quick look at this query, right? I’m not going to run this here, of course, because here is not where we want to run this.
We want to look over here. So I have query plans turned on and I’m going to run this query with no indexes. If we do this, this runs for about a little over half a second.
And it does a lot of work over here and it does a lot of work over here. And, you know, it’s acquiring data down here from the post table where it’s also updating, right? So we need to find data that we want to update in the post table by like joining it to the users table.
If we wanted to reduce the risk of this query colliding with itself or with any other queries that are touching the post table, we would want to add some indexes in that help this query go faster. So if I, I mean, we don’t necessarily, we don’t strictly need the index that I’m making here on the users table.
It’s just to sort of illustrate that the more you do to help your modification queries go faster, the simpler and the simpler your modification queries are, the query plans are, the faster they go, the better up you are when you need to deal with these things.
So these indexes are created here. And now let’s just rerun this. And remember, this was like a little over, a little north of half a second. But now with those two indexes in place, this thing runs for about 19 milliseconds, right? And it’s like, so just, you know, coming over here and looking, we no longer need a parallel plan.
SQL Server, just, you know, be able to seek to data that it cares about on both sides of this. Everything is much better, right? So like when, if you’re like serious about query tuning, don’t just stop at the select queries in your workload.
Make sure you’re tuning any modification query that, you know, has like a query underneath it. If you’re inserting values, what are you going to do, right? There’s not a lot you can do there.
But like, you know, updates, deletes, even if it’s like an insert with a select, you know, don’t be afraid to tune those queries as well. Like modification queries need love too. So with that out of the way, let’s talk a little bit about how indexes can help you under read committed.
Now, I’m going to go off on something a little bit here where if you are truly worried about over indexing and you are truly worried about locking and blocking, the answer is not no lock. If you are using an optimistic isolation level, like read committed stop shut isolation, not only would you avoid the many, many pitfalls of no lock, but you would also avoid having to sort of over index tables because read queries and write queries would no longer be blocking each other or deadlocking with each other. So if you’re truly worried about it, optimistic isolation levels can help you get out of lots of blocking and deadlocking scenarios without having to tune queries over index.
Oh my gosh. I can only have five indexes on this table. If I create a sixth index, my workload will go to hell.
So like just, you know, one thing to consider is, you know, what isolation level are you using? If you’re afraid of indexing too much, use an optimistic isolation level. You won’t have to index as much.
You’re blocking and blocking problems and deadlocking problems will largely go away. You’ll still have write queries fight with each other, but that’s okay, right? Because, like I said, who’s worried about their write queries anyway? So let’s begin a transaction and let’s run this update query.
And, of course, this thing switched over to that mode for me. So what we have here is the results of my handy little helper function, What’s Up Locks. This is available at my GitHub repo.
If you go to code.erikdarling.com, you can get a nice little shortcut there. But if you look at the results here, we have an intent exclusive lock on the object. That’s not what we’re worried about.
We’re worried about this exclusive lock on 11 pages in the badges table. Now, if we come over to this window with, like, this query is open. We’ve done the transaction.
We just got the results of What’s Up Lock. We didn’t commit or roll anything back down here. If we look at this query, this query will be able to finish. Like, you know, this query is able to finish because we’re seeking into the primary key right here, where this ID equals this.
And this ID is just outside the range of pages. Now, remember, pages contain many rows. So if, like, this query is able to finish, but this query that is just one ID higher, this one gets stuck.
And if we come over here and look at SP who is active, we’ll see the locks that this thing has taken, which is exactly what I showed you before. Right? It’s the 11 page locks on the badges table.
Right? There we are. Like, my results line up with SP who is active. I consider that a real feat. It’s a pretty good deal.
And then this query is trying to take some locks, but the object level shared locks got granted, but the page level shared locks, we are waiting on that. So this query is blocked because this query could not get to the data that it cared about. It was like this row was on a page that is currently locked by this query over here.
Right? So let’s roll this back. Let’s make sure that’s double rolled back.
And let’s create this index down here on the date column on the badges table. Now, remember, this update query is hitting the badges table. I’m just going to get the estimated plan for this.
We’re able to seek into that index now. Before, all we had was the clustered primary key on the ID column. So we could seek into the ID column, but if pages in that ID column that had dates in this range were on those pages, or even like if dates a little before or a little after happened to be on these pages, those queries got blocked.
All right? So now let’s run this with that index in place. And what we’re going to see is the pages have intent exclusive, so not actual locks.
And the object still has intent exclusive. Now, though, we have granted 856 row locks. All right?
Oops. Sorry. Makes a little more sense over here. Here are our row locks. Here are the 856 row locks that have been taken on the badges table now with that index in place. And if we come over here, now we can run this query.
And now we can run this query. All right? These both are able to finish now because SQL Server is only locking rows. And so the pages that are unrelated to data that was on pages that are unrelated to those rows, we can now get to that data.
We can access that data. We have a different access path to see that. Now, really, no demo about locking and blocking would be complete if we didn’t make fun of no lock just a little bit.
Right? And so if we run this query, and keep in mind, this where clause here matches the where clause of the update that we ran over here. Right?
This is 2010, 1225 through 1226. If we run this query with the no lock hint, we will, of course, see all of these user IDs change to what we, or rather displaying now what we change them to in this query that just has the open transaction. Right?
Like this query is open. We were changing every user ID to this that was in that range. But, of course, let’s say this query failed or someone changed their mind or we decided to go do something else and we roll this stuff back. Now this query is going to show us what those user IDs were before, which has nothing to do with the number that we just had in there.
So, this is something that I talked about quite a bit in the video series that I mentioned before. Everything you know about isolation levels is wrong. That playlist is available again on this channel.
But, you know, what I talked about before is that these are the kind of problems that a lot of developers hit under the default read committed isolation level. And they have decided that the situation with read committed is so bad that they are willing to have data problems like I just showed you with the no lock hint rather than deal with the problems that read committed exposes in your database and your workload. So, like, you know, like, like everyone likes to make fun of no lock, me included.
But really, like, no lock is what people choose because read committed is so crappy. So, if you’re using read committed and you’re, you know, you’ve got this idea in your head that no lock hints are a good solution, they’re really not, you should be looking at optimistic isolation levels that avoid dirty reads like I just showed you that return you a far more stable and correct set of data without the locking and blocking problems. So, I think we’re about done here.
Indexing your modification queries can absolutely help them not only go faster, but it can also help reduce the number of locking and blocking problems that you have in your database. Of course, the ultimate solution to locking and blocking and deadlocking problems between read queries and write queries is an optimistic isolation level. I can’t say that enough.
So, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that if you haven’t seen my series on isolation levels already, you will go find that playlist and watch the whole thing. It is well worth your time.
And thank you for watching. Now, we’ll see you in the next video, which is about more indexing stuff. So, we’ll see you there. All right.
Goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.