Learn T-SQL With Erik: Indexed View Serializable Locking
Chapters
- 00:00:00 – Introduction to Index Views and Blocking Issues
- 00:03:45 – Isolation Level Promotion to Serializable
- 00:06:51 – Blocking Analysis with SP_WHOISACTIVE
- 00:09:28 – Trace Flags for Detailed Locking Information
- 00:11:56 – Summary and Next Video Preview
Full Transcript
Apologies for the low audio in this video — I was testing a new microphone setup that didn’t work out. Should be back to normal volume in future videos.
Erik Darling here with Darling Data, now with a fully turned on microphone. In today’s video, we are going to go through a little more snippity-tidbitty tiny chunks of material from my larger Learn T-SQL with Erik course. You can buy that in a very cool way by looking down in the video description.
If you look on down yonder, what you’ll find are all sorts of helpful links. Helpful to both you and I. Helpful to you because you can get help, or you can get training.
Or you can support this channel so that I don’t turn it into an Amiga repair channel. And that gives me money and it gives you SQL Server stuff. Fair trade.
You can also ask me office hours questions. And of course, if you enjoy this content, please do like, subscribe, tell a friend, family, foe… I don’t know, tell your foes too. At least, maybe they’ll get so into this channel they’ll just become completely distracted.
You’ll no longer have to worry about them attacking you in some way. But the training in question does have a coupon code linked down in the video description if you are the type of person who needs, wants, requires, or enjoys high quality SQL Server performance training.
For free, also linked down in the video description, my SQL Server performance monitoring tool. It’s almost like if you hired me to come work for you.
And you said, hey Eric, we got these slow SQL Servers. How can we figure out what’s slow and how can we make them faster? And this is the stuff that I would look at. I might even bring my own monitoring tool in and be like, hey, we’re going to start looking at this stuff.
And if you need help with looking at your monitoring data, I have enabled you to opt in to turning on some MCP tooling servers. And so you can have your robot friends look at your, collected performance data and only your collected performance data in a read-only way so that you can figure out what’s going on with your SQL servers.
We are down to two. Because this is publishing while I’m in Poland in Wroclaw for SQL Day. I’m sure I’m having a great time.
I’m sure that I haven’t been arrested and I’m sure that I am very healthy and not hungover. All things that I’m sure are true at the time of this thing. Going live. But I still have some other things that I’m doing out in the world.
Data Saturday Croatia, June 12th and 13th. I have an advanced tickling pre-con. If you…
I mean, maybe I should. There’s probably better money in a tickling pre-con than a T-SQL pre-con. But I have an advanced T-SQL pre-con at Data Saturday Croatia. And I will be attending PaaS Data Community Summit in Seattle from November 9th to 11th.
And some manifestation. We’ll see what happens. But for now, it is May.
And I hate Star Wars. So we’re just going to keep going here. All right. Because that day happened. I am much more of a Cinco de Mayo person.
National Margarita Day, also a personal favorite in the Darling Data household. 100% of SQL Server monitoring tool moguls prefer… National Margarita Day to dumb Star Wars days.
Anyway, let’s go look at IndexView stuff. Now, IndexView maintenance is a very, very complicated topic. It requires a good bit of caution when discussing all of the different aspects and behaviors that get involved here.
For a single table IndexView, locking is pretty normal. You take some xlocks. And the transaction isolation level is left alone.
The query plans can get interesting. But, you know, that’s just IndexView maintenance for you. Paul White, friend of the chain Paul White, has an excellent post called IndexView Maintenance in SQL Server Execution Plans.
Written back in year of our Paul, year of our Kiwi 2015. But things get much more complicated. And something that I would probably caution you against doing is putting multiple tables into an IndexView.
Because when you do that, that’s when things start to get funky. Both from the perspective of, well, I mean, the complexity of maintenance for that. The performance of maintenance when it comes to that.
And also, what happens to locking and isolation levels behind that. So, let’s look at an example. I’ve already pre-created this because it takes a little bit to get set up.
But I’ve got this IndexView called UserPostScore. And it is joining the user’s table to the post table in the StackOverflow 2013 database, StackOverlord. And I have created myself a unique clustered index on it.
So, if you want to, oh, yeah, that’s the thing. If you want to reproduce this code, this little function that I’ve got here. What’s up, locks?
This is in my GitHub repo, the darling data one. You’ll find it under, like, helper, use, functions, things in there. But let’s start this running. And what I want to show you first is when we update the user’s table, right, we’re going to look at locks for the user’s table, the post table, and our IndexView, right?
So, if we run this, this takes about five seconds because I haven’t done any tuning work on it. It’s sort of intentionally bad. We’ll see that we have regular xlocks on users.
We even have some regular xlocks on the IndexView UserPostScore. We do not have any locks on the posts table, right? But notice this third line down here.
We have these range xxlocks taken on the IndexView. These are serializable locks, right? So, our isolation level has been silently promoted up to serializable. And that means, of course, that, like, you know, under the default index view, it’s going to be serializable.
It’s not read committed isolation level for most SQL Server people who are, you know, just refuse to listen to me about using read committed snapshot isolation. That means that if we try to run a select from the IndexView, we’ll get some locking that is sort of expected, right? We’re updating rows in the user’s table that affect rows and columns that are in the IndexView.
So, getting blocked here is, like, pretty acceptable. What’s weird. Well, rather, it’s acceptable under the unacceptable garbage isolation level read committed.
But if we also run this and we try to update the post table, the score column in the post table, this gets blocked, too, right? So, let’s come over here and let’s look at SP who is active. We’re going to use my favorite personal way of, why does that say execute?
There are more letters in that word. One of my personal favorite combinations. Of parameters for SP who is active.
Get additional info and get task info. This is a much more lightweight way of seeing blocking. There is the get locks parameter as well. So, if we say, let’s get some locks here.
Get some else socks. If we say that, why are you trying to fight in here, Management Studio? So, if we say get locks equals one, we will, of course, get this locks column.
And this locks column will tell us quite a story. For the query that, oh, that’s the next video. Sorry about that. Now we’re all spoiled.
For the query that’s doing the blocking or doing the locking, you know, there’s not a, maybe, there’s some interesting stuff in here, right? Some of the intent exclusive in the xlocks. And we see the range locks in here.
But this can often turn into sort of a lock for SP who is active to enumerate when there’s a lot of locking going on. So, that’s why I prefer get additional info. And get task info.
Because if you scroll over to this additional info column, for the query that’s doing the locking, it’s not going to show you anything all that interesting. But for the queries getting blocked, you will see what type of lock is being taken, the object that it’s getting hung up on. And so, the select query is, of course, blocked trying to read from the index view.
And the update to posts is now getting blocked on the user’s table. Well, that’s weird. All right.
Why does updating the post table get blocked on the user’s table? Well, it’s all about that index view, right? So, let’s get rid of these things. Let’s make sure we are good citizens and roll back our transaction.
Remember, kids, if you begin a transaction and you just hit cancel, it doesn’t roll back or commit the transaction, right? You have to actually tell SQL Server what to do. And let’s also kill this one here.
We don’t need to do that. We don’t need to keep running that. So, let’s also. We should roll this back as well because we don’t need that thing sitting out there in the breeze. So, the way that you can see what happens is by using a bunch of trace flags.
I believe that if these aren’t in Paul’s post about index view maintenance, they’re in another post of his. So, of course, you know, I take no credit for the discovery of this, but I do want to make sure that it continues to be discovered. So, it’s going in here.
So, if you turn on these trace flags, 8606, 8607. And you run this update, you will get in the messages tab a whole bunch of stuff, right? There is a lot going on in the messages tab.
I’m not going to try to scroll to find all this stuff here. But there are a few points in here that you will see if you run this and scroll through everything. Most importantly, these two lines.
So, on the post table and on the user post score index view, SQL Server internally applies these hints to the query. You can’t see them. They’re not in any of the block process or deadlock report XML.
They’re not even in who is active because, again, the optimizer, the engine, let’s call it, adds this stuff in when the queries execute. But you’ll see the serializable, internal. Detect snapshot conflict in case you have snapshot isolation on.
And then the one on user post score is updelock serializable detect snapshot conflict. So, SQL Server, this is the case where it’s silently upgrading your isolation level to serializable from whatever in order to make sure that the index view maintenance maintains referential integrity. So, very cautionary thing against using multiple tables in an index view.
I believe I’ve said it in other videos. Index views should be on a single table at a time and should be very generic in their aggregates. Typically, you don’t want to gear them towards just one single query because it’s far too specific.
They won’t get used a lot. And the impact that they may have on the entire database ecosystem can be sort of outsized for the amount of benefit that you’re getting from them. In other words, the cost of maintaining that index view is not amortizing or helping many queries.
Run fast. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something.
And in tomorrow’s video, we will be looking at something a little bit more query performance indexy. So, we’ve got that going for us. Anyway, 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.