A Little About Indexed View Maintenance In SQL Server

A Little About Indexed View Maintenance In SQL Server

---

Video Summary

In this video, I dive into the topic of index view maintenance in SQL Server, sharing insights from my experience as Erik Darling with Darling Data. We explore a common misconception about the overhead associated with maintaining indexed views and demonstrate how proper indexing can significantly impact performance. By walking through an example where we create and maintain an indexed view, I illustrate the importance of having supporting indexes to minimize the time and resources required for updates. This video aims to demystify index view maintenance and provide practical advice on when and how to effectively use them in your database design.

Full Transcript

Erik Darling here with Darling Data. In this video we’re going to talk a little bit about index view maintenance. We’re going to talk about index view maintenance and create this lovely shadow right here. It’s because a lot of people have a pretty big misconception about how expensive it is to maintain indexed views. Now, I fully admit that adding an index view is like adding another index to a table. There is overhead to it. You’ve got to make sure that everything stays up to date in there. And I do concede that when you start doing things like creating index views, filtered indexes, computed columns, you know, columnstore indexes, that you are probably dealing with a particular index view. pretty large volume of data and that you probably have a pretty good reason to be a little terrified about how you’re going to keep all these different things up to date and in sync. So let’s dive right in. And this is not going to be a, you know, very technical video about things. This is, if you want deeply technical, I would suggest checking out, you know, index view maintenance is probably three or four blog posts about that at this point, maybe some stuff about bugs as well.

That have, but I said, but anyway, twice in a row, it’s a good lunch today. So if we run this query to update the post table, we get a query that takes about three seconds total to run. This takes about three seconds total to run because we have to update an index, a nonclustered index on the table to update the clustered index on the table. And right now we just don’t have a very good supporting index to find the data that we care about in the table. Okay. So, uh, bench line there, right? Two points. Now, if we, this view, uh, I say, I assure you, it has a very good reason for being created.

And we create this index on that view. Let’s create this unique clustered index. Uh, this index is going to take a little bit of time to create, which might be our very first sign that we’re going to have trouble doing index view maintenance, probably because we don’t have a very good index currently on the votes table to support creating the index view. When you create an index view, SQL Server has to run the query that creates the index view. When you want to modify the index view, you have to run the query that creates the index view to modify it.

If creating this unique clustered index on the index view, you can probably imagine that modifying the index view might take a decent amount of time. So let’s try that. Let’s run this and we’re going to do that same update that we did before. And we’re going to roll it back. And I’ll just keep in mind that I do have accelerated database recovery enabled for this database.

So I do not have to worry about the rollback time on this. The only things that I’m concerned about in this query are how long it takes to do that update. We went from like like 2.8 or so seconds to from read. And I think 12.984.

It’s probably close enough to 13 most people. I don’t know if he ran this a thousand times and took the average and probably probably probably pretty sure that we would hit about 13 seconds there. But if we follow the yellow brick costs or rather the yellow brick operator times in this query plan, we’ll find that most of the time starts We have to sort of assemble that index view in the We have this clustered index scan on votes takes about seven seconds.

We have, you know, some hashing things going on here. This one spills a little bit. Coming over a little bit more in the query plan. This takes about 10, 11 seconds in here.

And then finally, when we add in the 11.454 at the end of the branch that assembles the index view to the 1.5 seconds we get that it takes to do the actual update. We get about 13 seconds total. So doing a little bit of query plan analysis, we can probably guess that if we had a better index to support the index view being created, then we would have better performance creating or assembling the index view when we need to do that for modifications.

So let’s create index on the post ID column of the votes table since that’s really just about the only thing that we care about in here. From the votes table, you know, we’re joining on that column here. Everything else is pretty post centric except the ID column from the votes table.

But, you know, that is unique column anyway. And since it’s the primary clustered primary key of the table, it will be implicitly a key column in the index we create on the votes table. So now with an index on votes, let’s try this update.

Cool. Back to about three seconds. Remember that first iteration, that first modification that we did took about 2.8 seconds.

This doesn’t really add a significant amount of time on when we do this. So I’m pretty happy with that. If we look down here where the plan used to accumulate, used to be about seven, eight seconds in here.

We no longer have that. It’s about seven, 700 milliseconds. So a proper and properly supported indexed view in SQL Server. Too bird brained behind the scenes with it.

But, you know, I guess being bird brained is sort of a relative metric, isn’t it? Kind of is. Anyway.

After the clustered index view, we don’t really… …watching. I hope you enjoyed yourselves wherever you are.

Hope you learned something about indexed view maintenance. … … Final video of the day before I go to the airport.

Thank you. That was a really important journey after I come back. And bethe as well, the fact is for David and Tom and and use him to them to And to recognize our gender or his humanity has a more constant age. Thank you.

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.