Indexing SQL Server Queries For Performance: Indexed View Maintenance
Thanks for watching!
Video Summary
In this video, I dive into the intricacies of indexed views in SQL Server, exploring their maintenance and limitations. Indexed views can significantly enhance query performance but come with a set of restrictions that make them less than ideal for certain scenarios. I discuss setting up optimal conditions for index view creation and maintenance, highlighting common issues like subquery limitations and the absence of basic aggregate functions such as `min` and `max`. Additionally, I share practical tips on how to mitigate these challenges by creating supporting indexes, ensuring efficient query performance. This video is part of a series where we’ll explore more ways to leverage indexed views for complex queries and address some of the limitations they present in SQL Server.
Full Transcript
Erik Darling here with Darling Data. And fortunately, I remember to turn my microphone on at the beginning of this video, so I don’t have to do 15 more takes of this. That’s nice. In this video, I think the next three videos we’re going to be talking about, I mean, still indexing SQL Server queries for performance, but there are three things that I want to talk about when it comes to indexed views. Indexed view maintenance, how you can use indexed indexes, where you can’t normally create filtered indexes, and what was the other one? Fixing what would normally be sort of a non-sargable predicate, or making aggregate queries faster, specifically when you have a big query that has a having clause on it. So, one thing that we’re going to have to talk about right at the outset, is that we’re going to talk about the first thing. The first thing I want to talk about the first thing, but the first thing that we’re going to talk about is the first thing that we’re going to talk about is that all the restrictions and things you can’t do with indexed views, because there are a lot of them in SQL Server, and they are depressing and painful. So, we already covered this one, so I forgot to click, or maybe, I don’t know, maybe I clicked and nothing happened. It’s hard to tell sometimes. We live in a world of mystery, don’t we?
But if you want to support the work that I do, creating content for this channel, you can click the little link in the video description right down in here somewhere, thereabouts. Sorry, my finger disappeared. If you know me, that happens a lot. And you can become a member of the channel for as little as $4 a month. These fingers don’t disappear, do they? If you don’t have $4 a month to spare, for whatever reason, maybe you spent it all on Spare Ribs, liking, commenting, subscribing, all fantastic endeavors that you can embark upon in your daily life to keep me company on the internet. If the stuff that I’m talking about, say, gets into your head, and you’re like, man, I’m having these problems with my SQL Server, maybe hiring Erik Darling, maybe he can fix these problems with my SQL Server in exchange for money. And yes, I can. I can do that. Everything that you see me do here, I can do on your server. It’s not just this server that these things work on. It’s not this magical microcosm of SQL Server where I can solve these little problems and things get better. No, no, I can do this on your servers too.
Even if they’re in Microsoft’s cloud, which is just notoriously awful. So all sorts of things that can be done here. And as always, my rates are reasonable. If you would like some fantastic SQL Server training, I have it. No one else does. I’ll be honest. I’ve seen everyone else’s. Mediocre at best. You can get all 24 hours of my training for 150 USD. It’s a much better bargain than you’re going to find anywhere else on the internet. Unless you pirate these. And then there’s nothing I can do. But if you go to the link up there and you use the discount code there, you can get all of it. And there’s no subscription. It’s just yours forever.
It’s like when you used to buy a CD or a DVD. You just you just had it. You didn’t have to like pay 10 bucks a month to keep listening or watching. Funny, I guess. Upcoming events for 2025. Again, you will know as soon as I’m allowed to tell you. With that out of the way, let’s talk a little bit about these here. Indexed view things. So like I like I said, the first thing we have to talk about is some of the stuff that like preempts using index views. So really? See, I didn’t notice this when I first cracked this open, but apparently Microsoft is now advertising on their learn page.
That’s cheesy. Who would do that? All right. Kind of weenie. What kind of weenie advertises on their website? But if you’re going to create index views, this actually goes for filtered indexes as well and computed columns. And you expect your queries to use them off the like without any sort of interference or without throwing errors. You do need to follow these set options. So anti nulls, anti padding, anti warnings, erythabort, concatenal, yields null, and quoted identifier all need to be set to on.
Right? This is all says on. And then, of course, numeric round abort, this weirdo. That has to be set to off. So that’s probably the easy part. I say probably because there was one time when I was like, I’m going to create a filtered index. It’s going to be dope.
And this cold fusion application, which uses like the weirdest driver to talk to SQL Server, just started throwing errors everywhere. And I had to get rid of the filter on the index. And that was a funny day. But hey, you live and you learn sometimes, you know.
Mistakes from years past turn us into better people, right? We don’t experience any pain. We learn nothing. You can tell by maybe the number of tattoos that I have that I’ve learned quite a bit.
So there are some other problems with indexed views as well. And there are things that you can’t do inside of them. Some of them are worse than others.
So, you know, CTE. I don’t really care about CTE anyway. So screw it. Subqueries.
This is an interesting one. Because, you know, some people do have, you know, very pathological issues with subqueries in general. But this actually doesn’t really fully explain just how many limitations this introduces.
You cannot use exists in an index view definition. You can only use joins. You can only use inner joins.
You might see, if you look down here, there are many other types of relational table joining syntaxes that you cannot use. But subqueries actually prevent the use of saying, like, where exists something in this table that correlates to this other table. Which is wild.
You cannot put an exists clause in a SQL Server Indexed View. Huh. I know. I know. Basic stuff. Right?
There’s some other things in here. So this one is particularly funny to me. You can’t use distinct, but you can group by every single column. What do we think that is?
Hmm. Hmm. What is that? Funny. Funny, funny, funny. So, derived table expressions. So, if you wanted to, like, put a derived join in your index view, you cannot do that.
So, if you want to say, like, from select from this thing join the result of this select to, like, some base table, you can’t do that. You cannot do self-joins, whatever. I don’t know.
I guess that makes sense. You cannot use table variables, inline table valued functions, or multi-statement table valued functions. This makes sense, and this makes sense. This does not make a lot of sense.
Inline table valued functions are typically very simple things. You can only put, like, a select in there anyway, so that doesn’t make a lot of sense. And then there’s some stuff down here that, like, you know, kind of, like, who cares a little bit? Pivot and unpivot.
Screw you. Table sample system time. Full text search. If you’re using full text search in SQL Server, you deserve every ounce of pain that you experience. That is nonsense. Cube rollup.
Okay, whatever. You cannot have having in there, which is actually one of the problems we’re going to solve later. And these are interesting ones, I think. Union, union all, accept and intersect.
Again, basic relational syntax. Microsoft, what are you doing? What’s happening? Where’s that $250 billion a year going? I mean, I know you’re burning a lot on AI lately, but index views have been around for a long time.
How about, like, this much of an improvement? There’s something, actually. It should be in this page, but I don’t see it immediately.
Yeah. This is what I was looking for. So you have to use count big.
You can’t use count. That’s fine. You can’t use average. You have to use count and sum as separate columns. But I think the big one in here is this.
Well, actually, probably this. You know, like, one of the single most common things that you can… And this is funny for a reason.
I’m going to tell you in a minute. Some of the most basic stuff that you would want to aggregate in having an index view are min and max. And a long time ago, a guy named…
Decided that it was not worth maintaining a nonclustered index in the index view to support min and max. So we don’t have that option available to us. And, you know, like 20-something years later, I don’t know if we’re ever going to get it.
You can use greatest and least in index views. Those are newer T-SQL syntax from, like, 2022. You can put those in index views all day and everyone’s like, fine.
Great. So there’s a lot of restrictions on index views that are really annoying. And it would be nice if Microsoft invested, like, a little… A fraction of the money that they blow on OpenAI on improving SQL Server just a little bit before, you know, SQL Server slowly turns into COBOL and Fortran and whatever.
But there’s a lot of stuff with index views that’s missing. Like, you can’t do it. You can do it anywhere else. So in this video, we’re going to talk about index view maintenance. Because one thing that you do need to consider when you create index views is, like…
So, like, under, like, optimal conditions, having an index view shouldn’t be any more maintenance for your query than having another nonclustered index on the table or whatever. But, like, you do have to consider how that index view is assembled.
So let’s look at this query, right? And I’m going to run this update in the transaction so that we don’t actually change all these scores to a higher score and have weird results in the future.
But if you look at the query plan for this, this runs pretty quickly, right? Just a little over half a second. Like, I know, this could be better with an index. We just recorded a view on improving…
Sorry, we just recorded a video. Not a view. We just recorded a video on using indexes to improve the performance of modification queries. So, yes, I know, we could make this better.
But just stick with me for now. Now we’re going to create this view. And this view, I mean, we’re updating the post table here. And this view has the post table in it.
Now, there are plenty of warnings that go along with views that reference more than one table, especially around locking and blocking. Serializable isolation levels will kick in when these views need to get maintained.
So it really does behoove you to make index view maintenance as quick as possible. So we’ve got that index view… Sorry, we’ve got that view in place. Now we have to create a unique clustered index on the view in order to get it to be an index view, a materialized view, if you prefer that parlance.
It’s fine if you do. So this takes 20 seconds or so to complete. I don’t know. Hey, 20 seconds on the nose.
Look it. Right under my armpit. There it is. 20 seconds. Wonderful. It’s almost like I’ve done this before. But now when we go to run this update query, I just got the estimated plan real quick to show you that it gets a bit more complicated, doesn’t it?
And then when we actually run the query, this thing that went from finishing in 600 and something milliseconds, well, this is going to drag on a bit.
This is going to end up being, I don’t know, probably around like 12 seconds. 12. Look at that. Oh, sorry.
Look at that. 12. 1, 2. Right there. Look at that. 12 seconds. And the query plan explains a bit why. We have some problems in this query plan, don’t we? IndexView maintenance, if you’re not paying close attention, can be quite painful.
We spend about seven seconds scanning the clustered index of the votes table. SQL Server misunderstands this hash join and this hash aggregate. This adds a fairly significant chunk of time to the query plan.
We go from seven seconds there to almost 11 seconds there. And then, well, another second or so there. And then another sorty, spilly thing here.
So the indexView maintenance in this case is kind of gnarly. But if we were to pay attention to the query plan that SQL Server has there, there’s actually a missing index request that is helpful for like once ever.
SQL Server is like, hey, psst. If we had an index on this post ID column in the votes table, I think the indexView maintenance would suck less. And SQL Server, for all its faults, is actually right on this one.
If we had an index on the post ID column, that indexView maintenance would be a lot less terrible. So we created that. This index is there now.
And now if we go look at this update, this should only take a couple seconds. And so like this went from being 600 something milliseconds. I mean, like the time did double, which maybe isn’t great.
But if this indexView is quite helpful. And look, there’s even another, hey, if you create this other index, boy howdy, we can speed this up. And of course, this one is sort of right too because that is half the time we spent there.
But this we knew about. This we knew about before, right? We were aware of the situation when we first ran that update. We knew because we watched my other video on improving modification performance with indexes that if we had an index there, we could make this faster.
So if we were to create that index, this thing would take, this thing would be pretty quick all in all. Now, indexViews do need to be created for a good reason because you do introduce this overhead, particularly when that indexView references more than one table. You know, it can, again, like really puts in weird emphasis on locking with the serializable stuff.
So you have to be careful there. But indexViews when properly used and when properly indexed for can be very powerful things. So in the next two videos, we’re going to talk about different ways you can use indexViews to get around some tricky stuff in SQL Server queries and query plans.
And some limitations with other fancy indexing features in SQL Server. So we’re going to do that. I’m going to end this one here.
Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I hope that you will petition Microsoft to make indexViews not suck, finally. I don’t know.
Something along those lines. Anyway, thank you for watching.
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.