Meet The Aggregator

SQL Server really wants to use the nonclustered columnstore index on our new table, but things get weird pretty fast.

Transcript

This problem query is ‘The Aggregator’.

The aggregator is counting up the number of rows or the number of babies that were born in each year. It does this by grouping by the BirthYear column. It’s only interested in years between 2001 and 2015. Even though there’s more in the table we’re trying to count them up for those rows and we’re going to return the year with the most babies at the top.

Pretty straight forward, right?

Comparing performance: non-partitioned vs. partitioned table

Here we are in Management Studio in SQL Server 2016.

My non-partitioned table, I’ve got the query referencing it right here. It’s in the dbo schema.

The partitioned table is in the PT schema.

Exact same query, just one of them references the non-partitioned table, the other references the partitioned table.

I’m going to turn on actual execution plans for my session and then run both queries.

I warmed up my cache before I started recording this demo

All of the data is in memory and that’s just so the first run of the query doesn’t have to do extra work and go out and get a bunch of data from disk.

I try to do that in all of my recorded videos, the exception would be if I’m dealing with something where I don’t have enough memory to fit it in there, then I would try to read from disk every time I tested it.

I have my data back from both queries, and let’s take a look at the information in the plans

I’m running SQL Server 2016, so I get some really cool information about runtime in these plans.

Looking at the queries, one thing we can see right away since we ran them both at the same time, we can see right at the top that the estimated cost for the first query was 79%. When looking at the entire work that it had to do it was like, “wow, this first one is going to be more expensive.”

The second one it thought would only be 21%.

Cost is always an estimate when you see it in these plans in Management Studio

The SQL Server doesn’t go back at the end of the query and say, “I’m going to update the cost as to how much work it actually was.”

This is actually a really cool recent thing they’ve added, we can see within the plan in the properties, some interesting info. I’m going to right click on the select operator on the top query, go to properties– I like having my properties pane at the left now because this is so handy. It defaults to being on the right but I like getting to this a lot.

I have query time stats right in the plan - here’s how it performed against the non-partitioned table

I can see that this top query took 2.8 seconds, that’s the duration. It used multiple threads, it used parallelism, so the CPU time is higher than that. It’s almost 10, it’s nine and a half seconds on CPU time for the top query, but it finished in under three seconds.

What about the partitioned table?

How did my second query do? SQL Server thought this one was going to be cheaper.

Its elapsed time is a lot higher than three seconds. Its elapsed time was almost seven seconds, and it used 26 seconds of CPU time. What we think is going to be expensive isn’t always what ends up being expensive.

Our query at the top is against the non-partitioned table and that was finishing in under three seconds. It got slower against our partitioned table. What’s up with that?

Let’s compare the execution plans

Going to the right, our query against the non-partitioned table did an index seek, and if I look in the– this is the same info in the properties, just less of it. It’s either more or less readable depending on how you feel, but looking at the object name I can see that this used my nonclustered index on BirthYear and it did a seek. That makes sense. I want data between 2001 and 2015. So it was like okay, well your index is on BirthYear, the computed column that’s persisted in the nonclustered index. I can seek right to the beginning of where I need to start and read the rows for those years and stream it, I can count it as I go. It’s really cool.

Things get weird in the nonclustered columnstore in the execution plan

But against the– as soon as I partitioned the table, huh, SQL Server decided to use the columnstore index. That nonclustered columnstore index that I gave it, it was like hey, I’ll just go scan that thing. Well, how much did it scan of it? Was it able to eliminate partitions because I just want the data between 2001 and 2015? I’ve got that operator highlighted.

Let’s look at the properties window. I still have it highlighted. This is the columnstore index scan. Going to the top here, something does look kind of funny. The actual partition count is zero. Yeah that looks like we didn’t touch any of the partitions. How did that happen??? That just looks a little weird. We can tell from the actual number of rows that it really did scan EVERY partition in this nonclustered columnstore index. We’ve got all 159 million rows coming out of it.

This is kind of interesting because, like I said, we don’t have BirthYear in the partitioned index. Er, sorry, we don’t have BirthYear in the nonclustered columnstore index, which is partitioned. But just to be clear, it’s only the columnstore index we can’t put BirthYear in [in this version Of SQL Server]. Why is it using it for this query that cares so much about BirthYear?

Well, if we look at the next operator, the compute scaler. A compute scaler computes values based on what it’s getting in there. Let’s look at this defined values that it’s doing here. It’s saying okay, I have figured out that the BirthYear column is a computed column based on the FakeBirthDateStamp column. So what I can do is I can scan every row in the nonclustered columnstore index, and then I can push it into a compute scaler because I have FakeBirthDateStamp in the columnstore. I can push it into this compute scaler and figure out the BirthYear for each and every row.

Then I can feed it into a filter where I filter out – and let’s go here to the predicate in the filter. If I look at the predicate on the filter and then pop that up– now at this point, after scanning every row, figuring out BirthYear for every row, now I can drag it down to just the rows for those years you want. So it didn’t do partition elimination with this.

It’s just REALLY excited about it’s nonclustered columnstore index. Like I said this was slower.

And I mean – We’re doin' a lot of per row work there!

What if we tell it not to use the columnstore index on the partitioned table?

All right, well, maybe we should tell it not to use our columnstore against the partitioned table.

Our original query was under three seconds, right?

What if we run our same exact query against the partitioned table but say okay, don’t use the columnstore index.

Because I have a rowstore index on BirthYear. Just use the index on BirthYear, like you used to before [against the non-partitioned table].

Let’s run this query. I’ve got my actual execution plans on and here we go. And again I did run this before, warm cache, but just to prove it I’m going to go ahead and run it twice. Just to prove that I haven’t cleaned that data out of memory or anything.

Our plan is here. And again I’m going to look at the properties and click on the select operator to get the properties I want to show up in that window. Looking at query time stats, I am NOT under three seconds.

I’m faster than I was doing all that work with the columnstore index and figuring out BirthYear for every single row, but I am at four seconds.

I’m a full second slower. Using the partitioned index on BirthYear is slower than it was using a non-partitioned index on BirthYear.

Looking at the right, I did do an index seek. I’ve highlighted the index seek and I have my properties window still open.

How many partitions did I access? On this query was I able to just look at the partitions for the years I want? Looking in the properties window with this highlighted, unlike that nonclustered columnstore this is giving me very specific information.

The actual partition count is 56. That means I accessed 56 partitions. The numbers of the partitions that I accessed was from partition one to partition 56. So I really touched them all. Huh, so I was not able to eliminate partitions in this.

There are more differences in the execution plans

In our original plan against the table before we partitioned it, some things were different than in this plan as well. This plan has a hash match operator in it that’s doing a partial aggregate.

In our original plan we had a different operator there. We had a stream aggregate. That’s part of what’s making our query against the partition BirthYear index slower.

This is a little easier to understand with some pictures, so we’re going to switch on over to some slides and I’ll show you how these indexes are structured and why they work differently.