This video has captions you may enable in the viewer, plus a written transcript below.
More than you might think changes when we add a nonclustered columnstore index… but not right away.
I want to know: SQL Server, would you rather use a nonclustered columnstore, or would you rather use that nonclustered rowstore index?
The non-clustered columnstore does have every column that I need in there. It has FirstNameId, StateCode, Gender, ReportYear, and NameCount.
Having that nonclustered columnstore index on the table means that even if SQL Server chooses not to use that index, it now has batch mode operators available to it. Which is pretty cool, because batch mode operators… I mean I have to scan everything in this table.
Why is batch mode cool?
Batch mode operators mean that instead of the going rowsie-twosie, you know, one row, two row, three row… I can work with groups of rows, which for these bulk operations like I’m doing, I want to, you know, do these group by’s across FirstNameId, StateCode, Gender, figure out my running totals.
Hey, I don’t want to do this all row by row.
Alright! I’m going to run this with actual plans on.
We’re still using a threshold of five hundred, we’ve been doing that throughout.
So, what we’re looking at here is we want to know
- Does it pick the rowstore index or the columnstore index?
- Do we get batch mode on operators in the query
- If so, where do we get batch mode?
Now we’re at twenty two seconds already, were still not super-duper fast. Still things are kinda slow.
Looking at our execution plan, I’ve still got a warning here: my warning… looking at the properties of my select operator, we’ve still got that same type of warning: it’s an excessive grant warning.
The warning hasn’t changed there.
We did get batch mode on some operators. This sort operator right before it returns the data to us. Note that it’s not row mode! We got batch mode.
We still haven’t looked at whether it used our columnstore index, but just the presence of a columnstore index means that– when we are in batch mode, the actual time statistics are just for this operator, they aren’t cumulative for its children.
So this operator, the elapsed time on this sort was five hundred forty five millisseconds. The cpu time was one nineteen.
What was the maxdop?
To see how many threads were used – Ron was asking about the degree of parallelism– I only have four cpus on this, we can confirm that we had four threads, plus there’s a little watcher thread that’s assigned to say, “okay when are you all done?"– since you’re a parallelism related operator.
I am getting all four cores, maxdop is set at four, all four of them are being used.
So, we do have batch mode on some operators
This hash match also has batch mode here. So these operators right before we return data to the user, got batch mode. What about down here?
Let’s look at our window spool operator. Our windows spool operators is in row mode. Just because we have batch in some places doesn’t mean we have it everywhere.
And looking over here, here’s our other window spool. This is in row mode as well. Which index did we use?
It decided, oh I want to use that rowstore index
So we created a columnstore index that it COULD use. It decided: I’d rather use that rowstore index.
Now. we did get a slightly different plan, because we did get batch mode on some operators, but they aren’t actually the operators that are taking all that time. Because it’s pulling the data out of a rowstore index, SQL Server currently only wants to use row mode operators to process that as well.
I’m going to drop that rowstore index
I’m going to say: Now your choice is between scanning The disk space rowstore clustered index, or using the nonclustered columnstore index.
NOW which one will you pick? All I have changed is the things it has to choose from, and said okay you’ve got to either scan the clustered index or use the columnstore.
Okay, we’re down to eight seconds!
We were always at like twenty five seconds before. And, it was just like, even when it had the columnstore, it was choosing to use that rowstore index.
What’s different about our plan now? Let’s take a look. We now have a columnstore index scan. It decided not to scan the clustered index. The sort operator is back. Because we’ve got – before we start doing our window functions, it’s like, just like when we scanned the diskbased clustered index, I need to have these ordered by your partition by columns, your order by columns, I also need to bring along that NameCount that we’re doing a running total across.
But then we go straight into something different. Before we had segment operators and we had a window spool operator, right? This is a new operator!
This is a window aggregate operator
If we look at this operator’s properties, this is a batch mode operator. This was introduced in SQL Server 2016.
So, you can’t get this operator – this is why this demo, you can’t see everything in older versions. We only got this operator in 2016, it is batch mode only. And it is really fast.
Notice that we don’t have those segments! This does the work of multiple operators. If I look at the properties on this, and I look at it actual time statistics,
Two hundred nineteen milliseconds! WOOO! They’re really awesome…this window aggregate operator is really, really awesome. But SQL Server, even when I had the nonclustered columnstore, just because it was deciding to still use the rowstore index, wasn’t giving me this operator, for some reason, right?
For this query so far, I’ve only been able to get this if I took the rowstore index away. We’re going to see if we can get it with the rowstore index.
What I’d really like – in my ideal world, I don’t want this sort operator. I want to presort in a rowstore index, but I want my new super-fast window aggregate operator
‘So what I’d really, really like, is I would: yes, yes, please, I want my window aggregate operator! But, if I can get it to use a rowstore index instead of a columnstore index: and the rowstore indexes is tuned for my partition by and order by columns, then I don’t have to sort.
And that saves me time!
I want to have my cake and I want to eat it, too! I want to presort in my rowstore index and then I want to use this fancy new operator…