How To Fix Blocking In SQL Server With Better Indexes
Video Summary
In this video, I celebrate reaching the 2,000 subscriber milestone on my YouTube channel, Darling Data. It’s been an exciting journey, and to mark this achievement, I’m planning a fun celebration for when I hit 10,000 subscribers in the year 2033—specifically, by sending out a pastel crayon portrait of Paul White as a thank-you gift to my lucky 10,000th subscriber. This video delves into how indexing can help resolve blocking issues, using a series of demos to illustrate the impact of index key column order on query performance and locking scenarios. I also touch on why read committed isolation level can lead to more aggressive locking and emphasize the importance of optimizing indexes for modification queries to minimize blocking and improve overall efficiency.
Full Transcript
Erik Darling here with Darling Data. And calls for minor celebration. It’s not a birth or a birthday or anniversary or even someone you don’t like dying. Just kidding. I don’t want anyone to die. I just want some people to live on a different spiritual plane than me. But I do have a minor or I guess major depending on how you look at it. Milestone to celebrate in that I crossed the 2,000 subscriber mark for my YouTube channel. Last I checked I was up to 2008 or 2009 of them. So, you know, that sort of explosive growth deserves celebrating. And to celebrate the explosive growth, and this is going to look a little funny on the green screen, is when I hit 10,000 subscribers in the year 2033, I’m going to send someone this pastel crayon portrait of Paul White. I did, not a child. A child didn’t do it. That was all my hand. Of his social media avatar. So, the lucky 10,000th subscriber will get one of those. I don’t know. That’s about it. But I’d like to thank you, constant watchers. Sorry, constant watchers.
It sounds a little bit creepy, doesn’t it? Constant viewers for liking and subscribing and engaging with me here on YouTube. I do appreciate your time. Even though, you know, I like to think that you get something out of it too. Aside from just getting to stare at me with the video muted. Hopefully you do learn some stuff about SQL Server here. So, let’s start off. And in this video, I’m going to talk. Actually, I got one, I don’t know, five videos, I think, to record. And they’re all going to be semi-related. I’m going to use a similar set of demos to talk about different sets of things. This one is going to be about how you can use indexing to get out of some blocking scenarios. Now, we’re going to create, we’re just going to do a little pretend here, you and me. A little game of pretend.
Where we have this index for various reasons. And it’s already created on the table. Maybe it’s good, maybe it’s bad, maybe it’s nothing at all. But we don’t even know if this thing, maybe this thing doesn’t even get used, right? But it’s going to help us show the effect of index key column order when we’re dealing with blocking queries.
So, we have this query here. It’s a select query. And the query plan looks like this. We do a simple seek into the clustered index. Now, it is worth noting at this juncture that the type of blocking we’re going to see here is only possible under read committed, the garbage, atrocity, isolation level.
If you are the type of bold, intrepid, intelligent person who has read committed, snapshot isolation turned on for their database, so you’re a forward thinking person, then you will not have to worry about this kind of blocking. If you are the type of Microsoft default loving, backwards compatible type person, then you might find this useful.
So, if you come over to this window, and I’m just going to use a begin train here to simulate a longer running transaction. And I’m going to do that so I can talk to you longer. It’s a stall tactic.
So, I can chat with you, Constant Watcher, longer by the day. So, the execution plan for this, you know, we, oh, go away, tooltip. We didn’t really need you.
So, the execution plan for this, if Zoom whatever decides to work, scans that nonclustered index, right? Since reputation is the second key column in the index, and it is an equality predicate in our update query, then we have to scan that index in order to find rows that qualify for that reputation filter, right?
Pretty straightforward there. I have a bunch of other videos about indexing, index key columns, why the order of them is important. You can view those at your leisure, since you are the type of person who has subscribed to my channel and likes my videos.
Now we’re going to run spwhoisactive with getlocks equals one. We’re going to use a getlocks equals one parameter, which disappeared on me right there, right? We’re going to use that to look at what locks this query has taken.
And if we actually come back over here, you can see that at this point, I’ve spoken for almost a full minute and a half since running that query. And again, that’s my little stalker stall tactic, so I get more time with you. So if we look at the locks that get taken here, we have this object level xlock.
And if we try to run this query, it’s of course going to get blocked. No fun. No fun there.
All right, this thing is going to be stuck forever and ever, right? 11 seconds now. Not a good time.
So, that’s not enjoyable. Now, if we change the index to have reputation as the first column, run spwhoisactive. Well, first we have to come back to this thing.
Run spwhoisactive. Getlocks equals one. We have a different set of locks taken on the table.
We have some page locks. We have some key locks. And the important ones to keep in mind here are these xlocks where the request mode is granted. All the intent exclusive ones, those ones are slightly less troublesome for our purposes.
But if we come back over here and we run this query now, you can see that we now get this result back immediately. We get this result back immediately. Oops, I went to the wrong window.
Oops. Lots of oopsing going on here. We get that back immediately because we are able to seek exactly to the rows that we care about and lock only the things involved with that. Only the direct things that we can see.
Only the keys involved for this query here. Right? And so because of that, SQL Server knows exactly which rows, keys, pages need to get locked much more quickly. Now, I’m going to speak in some generalities here.
The generalities that I’m going to speak in are that when you have a modification query and the foundation of that modification query where you, you know, access an index or even multiple indexes on a table to find the rows that you need, to find the rows or pages that you need to acquire, SQL Server will generally start with page level locks.
All right? So with the scan, you start with page level locks generally. Now, there are index options and other weird things you can do. It will change that.
But, you know, if you are the type of person who is using read committed, the garbage, atrocity, isolation level, then you are most likely the type of default loving person who is not going to mess with settings that might interfere with this demo holding true. And that usually results in higher levels of locking.
More things get locked. More things tend to escalate up to object level locks. Because SQL Server just really isn’t sure what it’s going to have to grab.
It’s going to have to get a whole lot more data when it’s scanning through and finding those data pages. When the foundation of a modification query starts with a seek, and I’m going to use the terms row and key locks here interchangeably, even though the hint that we get in SQL Server is definitely a row lock hint.
And there’s going to be a video about that as well today and why it doesn’t always work, and why it can actually cause more locking. Not more locks like in the time machine, but more locking.
But, yeah. When you start with the seek, you tend to get more row and key locks than anything else. Now, either one of those lock types can escalate up to an object level lock, which is what we saw in the first query.
Boom. You don’t see locks step up the chain more gradually.
Like, you don’t go from row locks to page locks to table level locks. You go from row slash key to table or page to table. Actually, it makes me wonder.
Now that I’m thinking about the row lock hint, I’m wondering why someone left the E out of the page lock hint, and we get a pag lock hint. That doesn’t seem like good English to me. But, you know, what do I know about creating hints for SQL Server?
Anyway, when you have locking problems, a lot of the times, at least when I’m working with clients and I find people who have locking problems, a lot of those problems come down to modification queries not having a good path to access the data they need to gather to modify.
You see a lot of non-clustered or clustered index scans, and all that results in more aggressive locking than you would probably want. As often as possible, you want to be able to seek to the data that you need to get to change so that you have more efficient modification queries and you have fewer chances of running into awful blocking and blocking scenarios under the garbage read committed isolation level.
Now, even under… Let’s stick to read committed snapshot isolation, because under snapshot isolation, you certainly can prevent some writer-on-writer blocking, but you also have to do a bunch of weird exception handling if the row that you want to modify has changed or has been deceased from the table when something else tries to commit its transaction.
So under RCSI, writers will still experience blocking with other writers, and it is still very much worth tuning the indexing for modification queries so that they execute as efficiently as possible and they stand as little chance of blocking each other as possible.
All right. So things we learned here today. Index key column order matters, right, for the way that queries want to access data in an index.
Modification queries need tuning love, too. You can make them faster, right? You can make at least the portion of them that gets data faster with more appropriate indexes.
You can resolve blocking problems with more appropriate indexes. And what else? Oh, yeah. Read Committed is a garbage isolation level, in case I hadn’t said it enough.
Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that you will be the 10,000th subscriber to my channel so that you can win this true-to-life, perfect portrait of Paul White.
I’ll even frame it for you if you’re number 10,000. Hopefully by then I’ll have enough YouTube bucks to get this really nice gold frame for this thing. Give it a home it belongs.
Museum-quality glass, no glare, right? Be nice. Anyway, I just had a weird video hiccup.
I have no idea what that’s going to look like in the recording, but I’m going to go now before anything else weird happens. So, yeah. Like, subscribe, all that good stuff. Thank you for watching.
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.