Summary and recap (4 minutes)

This video has captions you may enable in the viewer, plus a written transcript below.

To finish up, we’ll compare the performance of the different indexes and tuning techniques shown in the course. Then we’ll recap guidelines on how to design disk based rowstore indexes.

Let’s compare those timings

Looking at some sample timings for what we did: I wrote down… I ran these tests in the environment when I was NOT recording things / having a webcast going / using the laptop for all sorts of other stuff.

Here’s the timings I saw just for disk based rowstore indexes, first.

Disk based rowstore indexes & window spool operators

By just providing nonclustered indexes, we were able too.. I am able to make this faster, but it’s going from a twenty-six / twenty-seven second duration to twenty-two / twenty-three.

Because this is essentially just pre sorting the data first, but it’s still got a lot of work to do and that window spool operator still just a lot slower.

Nonclustered columnstore & window aggregate operators

With the nonclustered columnstore – and I did have to do some tweaking – I had to get rid of that rowstore index to get it to use the columnstore. It was deciding to still use the rowstore index while it was still there.

But when it actually does use the columnstore, we get the new window aggregate operator, and I can get down to about seven seconds. So a huge improvement, most of which is due to the window aggregate operator, not some magic of how the columnstore index is stored in this case.

Now in some cases the magic of columnstore index itself will just vastly speed certain things up. In this case, it really that new operator.

Hacking in the batch mode window aggregate operator with rowstore indexes

But the fastest performance times I can get are actually when I can get SQL Server to presort the data in a rowstore index, and get it to use that index. Which, in this case I had to use a hint to get the optimizer to use it. And, then I also trick it into giving me that new batch mode window aggregate operator, but I have to hack that in by – I created a non clustered columnstore index with an impossible filter.

So to get this fastest time of two-point-eight seconds, I had to create a hacked index, the behavior of which might be not stable, right? Because this is not a documented use of this by Microsoft. And then I had to add a hint as well.

But if that’s OK to do in my environment, I may be able to get the best performance, because the data is pre-sorted in a rowstore index just for this windowing function, and then I’m using the fast operator.

Remember the acronym “P.O.C.”

We’re talking about indexing for windowing functions in general: basic concepts.

That acronym of P.O.C. that Itzik Ben-Gan gave: look at the partition by, look at the order by, and then look for covering columns as well.

That number three, NameCount, we used as an included column.

Essentially when I created the indexed view, I was indexing by these things as well.

That makes sense, because when we ‘partition by’ and ‘order by’, we’re talking about: what are the sets that we need to group or order by? Those are key columns. Covering the NameCount prevented from us having to go back and look up rows in the base index.

If you’re tuning windowing functions and you’re in SQL Server 2016 and higher…

What I would really look at is: can I get that new window aggregate operator? Which is a batch mode operator.

I may or may not want to actually use columnstore for real, depending on how the table is used. And, you know, depending on the insert / update / delete rate of data to the table, right?

Even if you’re using columnstore for real, don’t just assume you’re always going to get the new window aggregate operator. We saw a case where I had that columnstore index on the table, it was fully populated, fully usable, and SQL Server was like: oh I’ll go use that rowstore index instead. And you’ll get your row mode window spool operator, too.

So, verify in your execution plans: am I actually getting the window aggregate operator? If you’re not using columnstore and you feeling lucky, maybe you just want to hack it in like we saw.