How Unused Indexes Hurt: Lock Escalation

Facts

---

Yesterday we looked at how unused indexes can muck up the buffer pool, because writes to them will bring pages in.

Today we’re going to look at how unused indexes add to locking problems.

Hold My Liquor

---

Video Summary

In this video, I delve into the impact of unused indexes on lock escalation in SQL Server. Starting off with a real-world scenario where I demonstrate how these unused indexes can significantly affect query performance by altering lock behavior, I walk you through an example update query and show how adding even seemingly helpful indexes can lead to excessive locking. By experimenting with various indexes and observing the resulting lock patterns, I aim to illustrate that having numerous indexes on frequently modified data can accelerate lock escalation, potentially causing more blocking issues. This video is a continuation of my exploration into unused indexes and their hidden costs, encouraging viewers to think critically about index quality rather than just quantity.

Full Transcript

Ehhhhh. It’s 8.30 in the morning and I’m recording this because I have a call in like an hour and I have some stuff that I got to do before that. I was going to record it last night but I got sleepy. Anyway, we’re going to talk about how unused indexes. Now, yesterday’s post was about how unused indexes could mess up the buffer pool, how they get going there when queries read them to put writes on them, right? To modify them. So here we’re going to talk about how that can change lock escalation. Now, the, like I’m not going to set up a weird workload to like say, hey, look at these indexes are so unused, blah, blah, blah. I don’t want you to think about these as just being like extra indexes. I want you to think about these being unused indexes. These are low quality indexes. They’re not like just extra. They’re not just like hanging around, but not like we had a certain number of indexes. You can’t have more than that because it’s just going to ruin your life. I just want you to think of it as a context. Like you ran SP Blitz index or something. You saw like unused indexes or you saw like indexes with a high right to read ratio against them and you want to know like what problems they might cause. There are a bunch of them. We talked about the buffer pool yesterday when we have to maintain these indexes when they’re written to and today we’re going to look at how they affect lock escalation. Now, the update query that I’m going to run is over in this window.

It updates everyone’s age where the user reputation equals 191 and of course I chose that number for a very specific reason. Now, let’s make sure that we don’t have any indexes going on here and we don’t. And I just want to show you really quickly that when we run this with no index on the table, we update 1500 rows. And when we look at the request mode, we have locked exclusively some pages. We have only asked for an intent exclusive lock on the object. The thing is without a helpful index to help us find user IDs with this reputation.

Let’s put that down a little bit. Without a helpful index to help us find user IDs of this reputation. This query just takes like excessive little of this where we’ll take like an object level lock pretty quickly. Right now. So, if we run this query, we can get one. And if we get this run this query, we can’t this this query is effectively blocked because this person has a reputation of 191, which is a record that we’re updating. So, let’s kill that and let’s roll this back and let’s add a helpful index and look how locking changes. So, that index is now in place. We’ll run our update.

And now let’s look at what we have locked. And now we only have intent exclusive locks on pages. We only have an intent exclusive lock on the object. And now we have exclusive locks on the key of an index. That index is the index we have the locks on is the one that we just created over here on the reputation column. So, we would still be able to select this data, but we wouldn’t be able to select this data because this data would still be locked.

Cool. Now, let’s roll this back and let’s see at what point of adding indexes does SQL Server say, well, I’m just going to lock the entire table. So, we’re going to add this index. And the point of all these indexes is that they all have the age column in them somewhere. So, that means that we’re going to have to modify them. Right? If they didn’t have the age column in them, we wouldn’t have to touch them.

But since these all have the age column in them and we’re updating age, well, that’s just a break. So, we added that index. Let’s go over here and look at this. Still 1500 rows. And when we run this, now we just have more locks on more keys. Okay? We went up from 1500 to 3100 something. Right? We effectively doubled the amount of locks.

So, now, go back and we’ll add our next index. What will happen now? Where will this go? We’ll do begin trend. Update that. Come back over here and look. Now, we just have more total locks over here. We still only have intent exclusive locks down here.

We only have the exclusive, like, I’ll mess with you locks up here. Fine. Roll this back. All right. Now, we’re going to add a full one, two, three, fourth, fourth index to the table. And when that’s in place, we’ll go look and see what happens. We’ll come run our update over here. We can trend 1556. What happens now? Well, 9000 locks up here. That’s getting up there, right?

And everything you heard about lock escalation is weird. All right. Let’s add another index to the mix. Let’s go get this one on there. And it was funny when I was practicing writing this, the amount of times I forgot to roll this back before trying to create an index. It was terrible. It was very unprofessional. And there was a lot of just terrible blocking involved.

So let’s go and run this now. And now we have, well, still attended the, but, but, but, but. And now we just have 10,000, almost 11,000 key locks. Ooh, boy, SQL Server. What are you up to? I’m being quite devilish, quite devilish this morning. It was being quite devilish last night too, if I do say so myself. That’s SQL Server. All right.

So now we’re going to go run this update again with a yet another index on the table and we’ll run this and 14,000 key locks, one exclusive lock. All right. Only on key intent, intent, funny, right? Funny stuff. All right. Roll you back. Messed everything up. Messed the whole thing up. Now let’s add one more index.

Let’s see what we add when we add this one to the pile. What happens? Begin turn, run that, see what kind of locks we have. And finally, we have an exclusive lock on the object. So now we have a full table lock, which means that if we wanted to run this count query, we couldn’t.

This query is now blocked. So the bottom line on what I was trying to, what I’m trying to get to you, get, get across to you here, is that when you have a lot of indexes on a table, you typically increase lock escalation when you need to modify data. Now, this is assuming that, you know, you have indexes on data that is frequently modified.

This can be very frequent for, you know, of course, inserts because inserts, you know, they just kind of write to wherever. Single row inserts, probably not, probably not as bad, but, you know, probably take quite a bit of that. But, you know, when you update data or when you delete data with a where clause, depending on how much data you’re deleting, you may see lock escalation occur much faster when you have more indexes on the table and you’re taking more locks.

Because the locks are, like, cumulative, right? So you may see lock escalation happen faster when you have more indexes. Now, there’s all sorts of ways to remedy this, right?

There’s all sorts of ways to get around, like, the terrible reader-on-writer locking that we were just looking at, you know, there’s optimistic isolation levels. There’s, of course, the fabulous, wonderful no-lock hint that I see everyone use so successfully and so happily. I’m kidding.

It’s a miserable experience for everybody. Anyway, that’s it for me. I’m going to go finish my coffee and, I don’t know, I’ll probably be embarrassed by how incoherent I was on this later. Anyway, thanks for watching.

I hope you learned something. I hope you liked the video. Maybe? Maybe? I don’t know. Maybe you did. Maybe you didn’t. Anyway, I’m Eric with Erik Darling Data. I’m actually Erik Darling, too, so if that counts.

Anyway, thank you for watching and I will see you in another video at another time. Maybe I’ll wear a cowboy hat in the video. It doesn’t exactly look like a cowboy hat.

Before I get my jacket, I perhaps Hong Haut, who will get on Tuesday? I certainly don’t know. I’m here waiting. Maybe I’m going to see one of my shoes. Sometimes I could walk a basketball over your shoes and do my shoes and do my shoes. Or there is a little惹 of a laundry thing from now, and then I will open any shoes. Maybe I can walk a covered aba or a cafeteria. Right, absolutely. Okay, sure.

Video Summary

In this video, I delve into the impact of unused indexes on lock escalation in SQL Server. Starting off with a real-world scenario where I demonstrate how these unused indexes can significantly affect query performance by altering lock behavior, I walk you through an example update query and show how adding even seemingly helpful indexes can lead to excessive locking. By experimenting with various indexes and observing the resulting lock patterns, I aim to illustrate that having numerous indexes on frequently modified data can accelerate lock escalation, potentially causing more blocking issues. This video is a continuation of my exploration into unused indexes and their hidden costs, encouraging viewers to think critically about index quality rather than just quantity.

Full Transcript

Ehhhhh. It’s 8.30 in the morning and I’m recording this because I have a call in like an hour and I have some stuff that I got to do before that. I was going to record it last night but I got sleepy. Anyway, we’re going to talk about how unused indexes. Now, yesterday’s post was about how unused indexes could mess up the buffer pool, how they get going there when queries read them to put writes on them, right? To modify them. So here we’re going to talk about how that can change lock escalation. Now, the, like I’m not going to set up a weird workload to like say, hey, look at these indexes are so unused, blah, blah, blah. I don’t want you to think about these as just being like extra indexes. I want you to think about these being unused indexes. These are low quality indexes. They’re not like just extra. They’re not just like hanging around, but not like we had a certain number of indexes. You can’t have more than that because it’s just going to ruin your life. I just want you to think of it as a context. Like you ran SP Blitz index or something. You saw like unused indexes or you saw like indexes with a high right to read ratio against them and you want to know like what problems they might cause. There are a bunch of them. We talked about the buffer pool yesterday when we have to maintain these indexes when they’re written to and today we’re going to look at how they affect lock escalation. Now, the update query that I’m going to run is over in this window.

It updates everyone’s age where the user reputation equals 191 and of course I chose that number for a very specific reason. Now, let’s make sure that we don’t have any indexes going on here and we don’t. And I just want to show you really quickly that when we run this with no index on the table, we update 1500 rows. And when we look at the request mode, we have locked exclusively some pages. We have only asked for an intent exclusive lock on the object. The thing is without a helpful index to help us find user IDs with this reputation.

Let’s put that down a little bit. Without a helpful index to help us find user IDs of this reputation. This query just takes like excessive little of this where we’ll take like an object level lock pretty quickly. Right now. So, if we run this query, we can get one. And if we get this run this query, we can’t this this query is effectively blocked because this person has a reputation of 191, which is a record that we’re updating. So, let’s kill that and let’s roll this back and let’s add a helpful index and look how locking changes. So, that index is now in place. We’ll run our update.

And now let’s look at what we have locked. And now we only have intent exclusive locks on pages. We only have an intent exclusive lock on the object. And now we have exclusive locks on the key of an index. That index is the index we have the locks on is the one that we just created over here on the reputation column. So, we would still be able to select this data, but we wouldn’t be able to select this data because this data would still be locked.

Cool. Now, let’s roll this back and let’s see at what point of adding indexes does SQL Server say, well, I’m just going to lock the entire table. So, we’re going to add this index. And the point of all these indexes is that they all have the age column in them somewhere. So, that means that we’re going to have to modify them. Right? If they didn’t have the age column in them, we wouldn’t have to touch them.

But since these all have the age column in them and we’re updating age, well, that’s just a break. So, we added that index. Let’s go over here and look at this. Still 1500 rows. And when we run this, now we just have more locks on more keys. Okay? We went up from 1500 to 3100 something. Right? We effectively doubled the amount of locks.

So, now, go back and we’ll add our next index. What will happen now? Where will this go? We’ll do begin trend. Update that. Come back over here and look. Now, we just have more total locks over here. We still only have intent exclusive locks down here.

We only have the exclusive, like, I’ll mess with you locks up here. Fine. Roll this back. All right. Now, we’re going to add a full one, two, three, fourth, fourth index to the table. And when that’s in place, we’ll go look and see what happens. We’ll come run our update over here. We can trend 1556. What happens now? Well, 9000 locks up here. That’s getting up there, right?

And everything you heard about lock escalation is weird. All right. Let’s add another index to the mix. Let’s go get this one on there. And it was funny when I was practicing writing this, the amount of times I forgot to roll this back before trying to create an index. It was terrible. It was very unprofessional. And there was a lot of just terrible blocking involved.

So let’s go and run this now. And now we have, well, still attended the, but, but, but, but. And now we just have 10,000, almost 11,000 key locks. Ooh, boy, SQL Server. What are you up to? I’m being quite devilish, quite devilish this morning. It was being quite devilish last night too, if I do say so myself. That’s SQL Server. All right.

So now we’re going to go run this update again with a yet another index on the table and we’ll run this and 14,000 key locks, one exclusive lock. All right. Only on key intent, intent, funny, right? Funny stuff. All right. Roll you back. Messed everything up. Messed the whole thing up. Now let’s add one more index.

Let’s see what we add when we add this one to the pile. What happens? Begin turn, run that, see what kind of locks we have. And finally, we have an exclusive lock on the object. So now we have a full table lock, which means that if we wanted to run this count query, we couldn’t.

This query is now blocked. So the bottom line on what I was trying to, what I’m trying to get to you, get, get across to you here, is that when you have a lot of indexes on a table, you typically increase lock escalation when you need to modify data. Now, this is assuming that, you know, you have indexes on data that is frequently modified.

This can be very frequent for, you know, of course, inserts because inserts, you know, they just kind of write to wherever. Single row inserts, probably not, probably not as bad, but, you know, probably take quite a bit of that. But, you know, when you update data or when you delete data with a where clause, depending on how much data you’re deleting, you may see lock escalation occur much faster when you have more indexes on the table and you’re taking more locks.

Because the locks are, like, cumulative, right? So you may see lock escalation happen faster when you have more indexes. Now, there’s all sorts of ways to remedy this, right?

There’s all sorts of ways to get around, like, the terrible reader-on-writer locking that we were just looking at, you know, there’s optimistic isolation levels. There’s, of course, the fabulous, wonderful no-lock hint that I see everyone use so successfully and so happily. I’m kidding.

It’s a miserable experience for everybody. Anyway, that’s it for me. I’m going to go finish my coffee and, I don’t know, I’ll probably be embarrassed by how incoherent I was on this later. Anyway, thanks for watching.

I hope you learned something. I hope you liked the video. Maybe? Maybe? I don’t know. Maybe you did. Maybe you didn’t. Anyway, I’m Eric with Erik Darling Data. I’m actually Erik Darling, too, so if that counts.

Anyway, thank you for watching and I will see you in another video at another time. Maybe I’ll wear a cowboy hat in the video. It doesn’t exactly look like a cowboy hat.

Before I get my jacket, I perhaps Hong Haut, who will get on Tuesday? I certainly don’t know. I’m here waiting. Maybe I’m going to see one of my shoes. Sometimes I could walk a basketball over your shoes and do my shoes and do my shoes. Or there is a little惹 of a laundry thing from now, and then I will open any shoes. Maybe I can walk a covered aba or a cafeteria. Right, absolutely. Okay, sure.

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.