This video has captions you may enable in the viewer, plus a written transcript below.
Our next tuning attempt: let’s pre-group the data and persist it using an indexed view. Will SQL Server use the indexed view? How much will it help performance?
Why try an indexed view?
One thing we might consider to do with rowstore disk based indexes is to provide an indexed view, because we can pre-calculate in an indexed view.
I’m going to create an indexed view you called IndexedViewAttempt. I’ve created this with schema binding, so that I can index it.
I’m doing a group by in here: what I’m doing is, okay I want to go ahead and regroup: it doesn’t pre compute the whole running total, but what we’re doing is: I took the partition by columns, that’s FirstNameId, StateCode, and Gender. Then I took the order by column, ReportYear.
Because we’re doing the running total, you know, and, then figuring out what was the year before, too. So I’ve got TotalNamed in here, I’ve got the sum of NameCount in here, and here is everything I’m grouping. I have to add the COUNT_BIG so it will let me add the index later.
Now I’m going to persisted it to disk. Once I create a unique clustered index on this view– any updates that happens– you there’s just one table in it, but any updates that happen, or inserts or deletes, any mods that happen to agg.FirstNameByYearState, it would also have to update my indexed view. This isn’t for free!
Let’s see, is it going to use it?
I’m just doing estimated plan here, I did CTRL+L, which is the same thing this button up here does.I like the Control-L shortcut for that.
And I’m just asking SQL Server, what would you use here? Give me your plan that you like.
And it says: “I would like to still scan my nonclustered index. I don’t want to use your indexed view.”
So, well, okay. You really like that nonclustered index because you think that sort is a huge problem. I’m going to just temporarily disable that nonclustered index.
This is a test system, one hundred-percent, I’m the only one using it, nobody else is impacted by me disabling or– in other words deflating that non clustered index, and making it unusable.
It’s disabled, so now, if I run my query and all it’s got as choices right now is:
- I could scan the clustered index, and sort all the data.
- Or there’s an indexed view that maybe I can use, that’s already done a group by on my partitioning columns and my order by column. And it’s got the sum of NameCount in there as well.
How much could that help me?
Well, we’re at twenty four seconds, twenty five seconds… We ended up at twenty seven seconds.
And, let’s see, did we use our indexed view? I didn’t change my code, my code doesn’t refer to the indexed view by name. Automatically matching indexed views is an enterprise edition feature.
It did figure out: hey I can use this indexed view based on the code matching
The window function can automatically identify an opportunity! That’s cool.
Even though I didn’t really make everything super fast.
And I don’t have that sort operator there. Let’s look in our second segment operator.
Everything’s still in row mode
Right now in SQL Server we cannot get batch mode in a case like this. Batch mode is another alternate mode, and we cannot currently get it unless we have some kind of columnstore index referenced somewhere in our query.
Specifically, unless we reference an object that has a columnstore index somewhere in our query.
It doesn’t have to use it, there just has to be an object that has one. So we’re just in row mode. By the time we finish up this segment operator, we’re at three-point-seven seconds.
Our window spool operator though, up to this point where it’s been what’s been slow. It’s still slow. We’re still at twenty seconds before we finish this thing up.
So we’re still spending– the indexed view didn’t magically save our window spool operator a bunch of work.
RATS! RATS I tell you. Rats.
Well, so, the indexed view is nice…? 😳 I mean, it was a good attempt. It was a great idea.
Now I’m going to go ahead and rebuild my nonclustered index. I have dropped my indexed view.
And, now I am rebuilding the nonclustered index, and making this index once again an option, because what we’re going to do next is create a nonclustered columnstore index…