The SQL Server Performance Tasting Menu: Why Read Queries Block Write Queries Sometimes

Posted on by Erik Darling

IMPOSSIBLE!

Video Summary

In this video, I delve into an intriguing scenario where read queries can block write queries—a situation that might seem counterintuitive at first but is actually quite common due to how SQL Server handles key lookups. I walk you through a stored procedure that selects data from a table with an incomplete index, leading to a key lookup operation that holds shared locks on the clustered index for extended periods. By simulating this scenario, I demonstrate how these long-held locks can block write operations, even though the read query itself completes quickly. This video not only explains the technical details but also highlights the importance of understanding execution plans and lock management in SQL Server to avoid such blocking issues.

Full Transcript

Hello, dear friends. I hope this video finds you all well. And in this video, I’m going to show you something that I had seen happen before and didn’t quite understand, and it took me a long time to figure out. Actually, I don’t want to even say figure out. I want to say it took me a long time to puzzle out exactly what was happening here. And then, of course, after I spent a long time puzzling this out, I found that other people had run into it as well, and it was… I could have saved myself a lot of time. But then again, you don’t really get to looking how I look by being smart. So, there’s that. Anyway, this video is about how read queries can block write queries. And I know what you’re thinking. This is impossible in other things and a bad French accent. But I assure you, dear viewer, it is absolutely possible. But it takes a little bit of engineering. So, what I have here is a store procedure that selects some stuff from the votes table. Now, over on the votes table, I have an incomplete index. This is not a covering index. This index is on creation date and vote type ID, which helps with my where clause, but not the column I’m selecting, grouping, or ordering by. And even though I’m doing some variable assignment here just to throw away results, it still ends up being kind of weird. Now, I’m going to run the store procedure with a very selective date value in order to get a bad plan. Now, this plan isn’t actually bad. It finishes very quickly.

But what it does is it sets us up with an execution plan where we do a key lookup back to the clustered index of the votes table. We’re able to do an index seek to locate rows that we want from our where clause. But we need to come back here to output the post ID column from votes. Right. Cool. Now, we’re going to introduce a parameter sniffing issue. Now, this doesn’t happen because of parameter sniffing. This happens because of a key lookup. But this is a really, really great way to get a key lookup to run for a long time so that I can capture the behavior easily. This is partially done out of laziness more than it being important to the demo.

So what I’m going to do is I’m going to kick this off. I’m going to come over here. I’m going to start an update. I’m going to count to like three or so. And then I’m going to come over here and run sp who is active. I’m going to kill this because I don’t actually want this to finish. I’m going to roll this back even though I don’t think there’s anything going on there.

Now, we can see over here that this query did finish. Right. This store procedure did finish. We did run for about 10 seconds here. And over in the sp who is active window, we have something very interesting. We have this select statement. Right. This is our select.

And we have that select statement blocking our update. Here is our update. And here is that update having waiting for about six seconds on an intent exclusive lock. If we go over a little bit further and we look at what this thing wants, it wants to lock the object and it’s waiting.

Look, it’s trying to lock the votes table and it’s waiting there. And I forget if this is interesting or not. Maybe sometimes it is. Not really. Object. Yeah. So over here we have a shared lock that has been granted on the votes table. Right.

There it is. Granted. Request count one. Status granted. Request mode shared. What this means is that we immediately get the rows that we want from the nonclustered index. Or we get a row from the nonclustered index. And then we spend all that time trying to read data from the clustered index to output for the rest of the query.

While we’re doing that, we hold a shared lock on the clustered index. Under normal circumstances, another non-lookup circumstances, I would say instead. Under non-lookup circumstances, reads are taken and released very quickly as we read through the index.

If we let go of that shared lock here and we let that update happen, there is the potential for returning incorrect data from one iteration of the key lookup to another. Right. Because if we let go of those shared locks and let’s say that we are reading, reading, reading, and something changed in the clustered index, we could return basically a mangled row of some kind. It wouldn’t like it would be like using no lock.

Something other stupid thing. Yeah. So, read queries, when they do lookups, can block write queries. You can see that right here.

Here, select, blocking a write. If you, out there in the real world, ever witness a read query blocking a write query, I will bet several dollars to several donuts that you have in the blocking read query lookup in your plan.

Now, there are some additional whoosie whatsits to this demo. So, our query did a key lookup, which a key lookup is always implemented using a nested loops join. There is no way for a lookup right now to be implemented via any other join type.

I don’t see that changing, so I don’t see any immediate relief from this. The nested loops join that was used in the query plan, which actually might still be up here. Yep, it is.

Look at that. Beautiful. If you go to properties, with unordered prefetch is true. So, we can see that over here. The nested loops join used in unordered prefetch to get data from the clustered index. And that caused shared locks, object level shared locks, to be held on the clustered index until the query finished.

If I had waited the 10 seconds out for that query to finish, then after 10 seconds, this would have started doing the update. But we caught about 6 seconds of blocking between the read query and the write query here. So, blocking isn’t always just writers blocking readers.

Sometimes readers can block writers as well. Fun stuff, huh? The things you learn. The things you learn in a day. That’s so much fun.

It’s so much fun and it just keeps getting easier to remember everything. That’s the nice part about getting old and enjoying wine is that remembering things gets easier. Anyway, thank you for watching.

I will see you in the next video.

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 performance problems quickly.

Related Posts