Learn T-SQL With Erik: Indexed View Maintenance
Chapters
- 00:00:00 – Introduction
- 00:02:16 – Index Views Considered Harmful
- 00:04:03 – Common Misconceptions About Index Views
- 00:05:33 – Disallowed Features in Index Views
- 00:08:44 – Performance Comparison Before and After
Full Transcript
Erik Darling here with Darling Data, and today’s video should be a fun one. We’re going to talk about making sure that indexed views don’t ruin your modifications, because they sure can under certain circumstances.
Down in the video description, you will see all sorts of helpful links, including a link to purchase the full course material. Remember, these are just tiny little crumbs of the full course material, which you can go get down in the video description below.
Or if you attend one of my upcoming advanced T-SQL pre-cons, you will also get the full course material for free 99. It’s amazing how that works.
You can also hire me for consulting. You can become a supporting member of the channel. You can buy me half of a New York City cappuccino every month if you’d like. Is it $4?
It’s not an incredible amount of money. And if you would like to ask me office hours questions, you can do that. And of course, if you are not feeling monetarily obligated towards me, which I understand many people just are very happy to just take things for free, you can like, subscribe, and tell a friend.
Fill a hole, fill a void in someone’s life that this channel would obviously, it would just complete them in ways that you can’t imagine. Or maybe you can imagine.
Maybe it just completed you. Maybe it just completed you in those same ways. Over on my GitHub repo, I have a free open source SQL Server performance monitoring tool. And it doesn’t suck.
It is all the stuff that I care about monitoring performance-wise on the SQL Server packaged up, given to you. You can point it at your SQL Servers. You can start getting great information about what’s going wrong with them performance-wise. Excuse me.
Spring is springing here, and now I’m starting to get a little allergic to things, so you’ll have to forgive my throat clearing on that one. There’s also a built-in MCP server.
So if you like the robots and you want to have the robots talk to your performance data and give you summaries or some analysis on them, maybe even give you some feedback on what you should do to fix it, you can do that. And they’ll do it, I mean, depending on how you call the robots, it might be free.
It might not be. I don’t know. All right. Tokens ain’t free, I guess. But anyway, this will be the last video that I record before going to… Jacksonville, Florida, May 1st for an advanced T-SQL pre-con.
You can still buy tickets. At least you better be able to still buy tickets, because it’s not for a couple days after this, so…
Or maybe… Actually, this one’s Thursday. The next day, Friday. Hey, you better hurry up before Jacksonville is gone. After I get back from Jacksonville, I will be on my way to Chicago, Illinois, May 7th and 8th for a pass-on tour east.
I will also be doing an advanced T-SQL pre-con. I will be doing an advanced T-SQL pre-con. I will be doing an advanced T-SQL pre-con there. Mmm. Many chances. After that, I will be at the lovely SQL Day in Poland, May 11th through 13th. And I’m flying right from Chicago to Poland.
It’s gonna be crazy times. Boy, I hope the weather’s nice. And in Poland, I know it might be hard for you to believe, but I will also have an advanced T-SQL pre-con there.
After that, I will be at Data Saturday Croatia with, believe it or not, an advanced T-SQL pre-con. And after that, I will be at PassData Community Society. I will be there. PassData Community Summit, the westest of all the summits in Seattle, Washington.
And there, well, I’m just gonna have to surprise you with what I’ll be doing there. It’s gonna be out of this world. Anyway, it is still April-ing outside.
Next week’s video will… Next week I will be debuting the May graphics. It’s gonna be wonderful and fantastic. You’re gonna be just as terrified of it as I am, I think.
Anyway. T-SQL Server Management Studio. When most people think about index views, they rightfully think about all the stuff they can’t do with them.
And I sympathize with that because, man, so many times they’ve been like, oh, if only you could do this, if only you could do that, it sure would be nice. And I realize that all the air has gone out of the room as far as making index views more powerful because everyone’s like, well, you could just use batch mode.
And that’s true in a lot of cases. But it’s also not true in a lot of cases. um batch mode is not always better than a completely pre-aggregated set of data so getting things like a min and a max in an index view you can’t do it and that sucks right this is lazy um but you know like really having those aggregations maintained somewhere uh can make read queries a lot faster especially when you consider that on standard edition um batch mode is still terribly hobbled everything maxes out at a dopp of two so if you still need like a dop above two like say four or eight or even six all right uh you don’t get it right it’s it just sucks and it’s annoying because you didn’t pay the friendship tax to microsoft but uh index use uh you know they have they have many challenges of their own uh we’re going to talk about those a little bit more i have another file queued up for next week’s stuff that we’ll talk a little bit index use in a slightly different way but um you know you know beside from the things that are disallowed in them uh you may you may still want and need no expand hints uh when you query them to keep uh sql server from expanding them into the underlying queries and ruining all the hard work you did to index that view just like filtered indexes and computed columns you need to have some anti-set options lined up correctly so that you do not experience terrible errors or queries not matching to your to your uh your your rocket science query tuning efforts um if you so you got to get this stuff lined up if you want them to work correctly but um we’re just to make things just like nice and compact here we’ve got this view which is not indexed yet but it is set up to be indexed by having a schema binding thingy here and a count big thingy here and of course we have the correct grouping that we need to do here this view already exists the problem is this view still takes 15 entire seconds to run right we are not having a good time with this view look at that well 14.2 seconds close enough uh you know sql server is like uh merge join that’s a good idea when is a merge joint ever a good idea right make that a hash join make that a parallel hash join and this thing would probably be about five seconds all right let’s say let’s try that let’s see what happens let’s do this and let’s let’s come on i’m clicking on you listen to me why don’t you ever listen to me let’s do a option i don’t know if you can hear the sirens outside but that’s another lovely side effect of spring the weather gets nice and i open my windows and new york’s like screw you here’s some sirens in your youtube video all right so let’s see let’s uh estimated plan what do we get look at that parallel hash join isn’t that a thing of beauty oh my word oh it’s gorgeous it’s wonderful let’s see what happens remember the last one was 15 well 14.2 seconds man wow parallel hash join 2.2 seconds actually let’s let’s go let’s go the tape i’ve been lied to by uh ssms before but yeah okay fine 2.2 seconds elapsed that is beautiful why would sql server pick a serial merge join plan when it could have had a beautiful parallel hash join plan i don’t know sql server sometimes i i i want to migrate to postgres when i see what you do but um when when we’re talking about you know uh trade-offs and query tuning and should i do this or should i do that i mean it’s our job to test these things right it’s our job to make sure that the the changes that we’re affecting have positive effects on the workload as a whole not we if we make one query a little bit faster but we completely ruin a whole bunch of other queries we didn’t do a good job right so we can’t have that but let’s let’s take a look right now uh at what an update to the post table currently looks like all right so i’m gonna do a begin transaction and a rollback and in the middle we’re gonna hit this little helper uh in a table valued function here called what’s up locks if you’re not familiar with this it’s available on my github repo with all my other grand stuff um so you can go get it there if you really want it but if we run this and we look at what happens when we update 100 rows uh we get you know we get a few x locks it’s not that big a deal uh the execution plan uh is pretty simple well for the for the update it’s pretty simple for for what’s up locks it’s clearly a complete disaster it’s well not a disaster but it is kind of a nightmare but uh here we have this thing and this you know we seek into an index and we do our update everything is just fine and dandy and even if we update 28 000 rows right if we hit john skeet and we say well it’s like 27 900 and something like you know we this thing does you know uh sort of lock the entire table but there’s no other competing locks so the lock escalation there not really uh any like all that unexpected for updating that many rows the execution plan does change a bit right but it’s it’s still a it’s still a pretty you know um you know still a pretty efficient plan for updating 27 900 and something rows but it can’t be too angry at that right that’s not not so not so bad but now let’s come back to our view here right and let’s let’s create a unique clustered index on our index view right and well this this takes a second to create and it’s not it’s like you know creating any other index it takes it takes a moment but but wait but while you’re sitting here and you’re sitting there waiting for that index to create you get to do all sorts of other things like run sp who is active maniacally and more manically rather and and like you know sit there and stare at your availability group and you’re blocking and whatever else and wait for it to finish but it’s finished now so that’s great and now with this done right with with this with this view indexed this all happens relatively quickly now we do one tiny little seek into the clustered index view uh sql server even suggests another index on here which we’re not we’re not going to add because we can just pretend 500 milliseconds is fast enough but now the the query plan for our update is going to change a bit right it doesn’t really get meaningfully slower for a couple reasons um i mean it gets a little bit slower but not like terrible and we have a lot more complexity in here now all right so because now we have to maintain the indexed view and so now we seek into the post table and we do all the updating we need to have the post table and then we have this sequence operator and the sequence operator says after you happen you happen i’m going to sequence you right and now down here we have to maintain the indexed view or rather the clustered index on the view which requires touching both tables in our case though i have added good indexes to support my index view so this is not a complete disaster right sometimes you do need good indexes in place to support the query underneath your index view to make reassembling the indexed view faster right it’s it’s a crazy world like like like when people say it’s like turtles all the way down it’s indexes all the way down it’s just oh i need an index to tune this and now i need an index to tune this and i’m going to make an index view but now i need indexes to make updating maintain maintaining my index view faster it does take some effort right and it does take some testing and stuff and uh updating 28 000 rows you know again this is half a second last time it it this does slow down a bit right it’s two points per second right again this does slow down a bit right it’s 2.6 seconds now so this is not the perfect world but now you can’t have everything all the time but uh you know we and we have a much more still a like this this plan was already a little bit more complex on top all right with all the sorting and splitting and filtering and whatnot but now maintaining the index view is still well it’s still like the bulk of our effort right that take that’s like you know you know there’s like 600 ah screw you there’s like 600 milliseconds here but two two two full seconds down here in the index view maintenance phase um you know most of it is not um assembling the index view right because we’re only at 325 milliseconds here that’s like 1.7 seconds total just updating the the values in the index view perhaps if we put that nonclustered index on the index view it would be it would be faster but you know let’s not let’s not get ahead of ourselves so if you want index views to work well for you uh you need to consider read queries and modification queries in your workload uh like with anything else modification query wise the more rows you get involved the longer something’s going to take a lot of the times um you know when i’m looking at trying to tune modification queries uh there’s like almost nothing from the read portion to tune everything you need to tune is in the right portion which is when things like batching become so much more valuable and useful and interesting to to get in because uh it turns out updating smaller chunks of work is going to take a lot longer than you think it’s going to take a lot longer than you think it’s going to take a lot longer than you think it’s going to take a lot longer than you think it’s to time well you may take the same amount of time to iterate over the table and update all 100 million rows or something uh you’re much kinder to your server in the process and things get a lot less like nuclear meltdowny when you’re doing that anyway thank you for watching uh yes this if you run a creator alter on an index view it drops all the indexes um but thank you for watching i hope you enjoyed yourselves i hope you learned something i hope that you will use indexes and index views responsibly in your SQL Server.
Thank you for watching. I hope you learned something. I think I may have already said that. Anyway, I’m good now. Goodbye.
Thank you. You are very kind people in the world. All right. Adios.
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.