Updates!

SQL Server 2017 allows computed columns to be in clustered columnstore indexes … kind of. The computed columns must be non-persisted. (They are still not allowed in nonclustered columnstore at this point.)

Transcript

I’ve run my setup script and I’m ready to go.

Here’s what setup did

First of all I made sure to have the database download that’s free to download from github. I restored that database. Then after I restored I did a few things.

I turned on Query Store and set that up, so that it would start tracking queries. We’ll check in and look at how things look in Query Store once or twice.

I created a nonclustered columnstore index on the pt.firstnamebybirthdate_1966_2015 table. We’ll be looking at that table in our first demo. Then I inserted some fake data for 2016.

The table only has data through 2015, but I added in some {fake} data for 2016 just for fun.

I added them in batches that are just under that threshold of the size that would automatically be compressed. So I added in these batches so that they would stay in the delta store, and I did eight batches of 102,399 rows– so we have some delta store data in our table.

We’re going to go ahead and get started with our demo.

There’s a statement right at the beginning that says: for the BabbyNames database, we’re going to wipe out the execution plan cache. This is the execution plan cache in memory, this doesn’t {clear out} Query Store. And this is just because we’re going to look at a couple of plans in our execution plan cache, and it makes the demo more easily re-runnable if you clear out {the plan cache} at the beginning, just in case you’ve run it before. If it’s your first time running the demo, that statement is optional.

Let’s take a quick look at our table

This table has a clustered index on two columns. The clustered index is on a big int identity column first, so that new rows that get inserted have an ever increasing identity column. That’s named FirstNameByBirthDateId.

The second column in the clustered index is FakeBirthDateStamp. That’s the column that contains a fake birthdate for each baby. The table is partitioned into years based on that FakeBirthDateStamp column. If we’re querying the table by FakeBirthDateStamp {only}, we can’t do a clustered index seek directly on that– because the clustered index leads on FirstNameByBirthDateId.

But if we can do partition elimination…

We can at least go straight to the partition that contains FakeBirthDateStamp based on partition elimination. Let’s take a quick look using our built-in dynamic management views– the partition functions, partition schemes, and sys objects– at our table and see the size of the partitions.

Our table has quite a lot of partitions and it has two indexes currently

The clustered index which is always index_id ‘1’. We can look at our boundary points here. Partition number one doesn’t have a boundary point. That’s because we’re using these lower boundary points.

The first boundary point we have in the table is for January 1, 1966 and it is a lower boundary point, so it goes with the data greater than it for 1966. Data prior to January 1, 1966 would all– if we had it– it all would have to go into that undefined place below the boundary point. So it is a place where data could go, we just don’t have any rows before 1966.

We have about 3.2 million rows for 1966, we have 2.7 million for 1975, our partitions are roughly equally sized, but not actually very large. For demo purposes, these don’t take up a ton of space– I wanted to keep the database relatively small. These are all partitions for our clustered index. We’ve got quite a few of them here. We go up to partition number 56, it is for 2020.

We have some here at the high end where we don’t have data yet: we’ve pre-created some empty partitions there.

Index_id ‘2’ is our nonclustered columnstore

Again for that partition number one: same thing, it’s created on the exact same partition function, it has the same number of partitions, this is just in a nonclustered columnstore index. When it comes to this table, the table itself has the same columns in it as a non-clustered columnstore with one exception.

The table has a column called BirthYear, that is a computed column. BirthYear is a computed column using the YEAR function, based on FakeBirthDateStamp. Computed columns cannot currently be in columnstore indexes, so our nonclustered columnstore contains the identity column on the table, it contains the FakeBirthDateStamp, and the Gender and FirstNameId.

It’s got all the columns in the table I could put in it, but we can’t put BirthYear in there because that’s not presently allowed. They’re very close to one another, and we won’t be using BirthYear in any of the demos today, so it’s maybe a little bit of difference in size, and it is a reason that I couldn’t just create a clustered columnstore index on this table. I would have to then vertically partition or handle BirthYear in some other way.

Let’s dive in now and start looking at these execution plans

Here is my first query. I do have actual execution plans already enabled– use this little button right here, or you can enable it with control + m if you want, I’ve already got it on.

Our first query we are doing is super simple. We’re saying: count the number of rows in the table where FakeBirthDateStamp is greater than or equal to January 1, 2015, but I’m not going to let you use that nonclustered columnstore index. So we are saying you can’t use the nonclustered columnstore, and the only other index it’s got is the clustered index– which is disk based row store. Since we said you can’t use the columnstore, we know that the only other option is to use that clustered index, there are no other indexes. This finishes in about seven seconds.

Looking at the execution plan, SQL Server is asking for an index, and there are some interesting things about this. First question: Was it able– and how can we tell– was it able to do partition elimination? I’ve highlighted the clustered index scan operator, and I’m looking at the properties here.

Since I’m in an actual execution plan, I get to see Actual Partition Count and Actual Partitions Accessed

Actual Partition Count is the total number of partitions that needed to be accessed by the query. It had to look at 56 partitions. Then the Actual Partitions Accessed is a range or a list, depending on what happened. What were the partitions? In this case it was just one to 56.

If for some reason it only needed to access partitions and they weren’t sequentially in order it would put commas. So if it needed to access partition one, partition 25, and partition 56 it would say 1, 25, 56. But in this case it uses the ‘..’ to indicate a range. So wow, it had to look at every single partition.

But the table is partitioned on FakeBirthDateStamp. And I’m saying I only want to look at the ones after a certain range.

Let’s see a little more about this. Now I clicked around, so it went to current connection parameters. To get back to the properties for the clustered index scan I have to do a little more clicking.

I can look at the predicates to see if I got partition elimination

There’s interesting things both in what I see and what I don’t see.

I see a predicate on my clustered index scan. I do not see something that says, ‘seek predicate’. That’s important.

When you see ‘predicate’ without the word ‘seek’ in front, it just says ‘predicate’, that is a filter that has been tucked into the operator, and it’s got to look at all the rows that it finds. We have no seek predicate, so we’re looking at all the rows and we’re applying this predicate.

In this case the predicate is– we’re checking: are you after FakeBirthDateStamp or not?

There is nothing in this plan that says– no partitions were eliminated, and we can tell from that, as well from the fact that it says, I had to look at all 56 partitions.

Well, here’s why. We’re actually using the wrong data type here. We said DATETIME2 here, we didn’t specify the precision. SQL Server assumes that if you don’t specify the precision, it assumes, “hey, you must want a really precise DATETIME2 number.” Our column in the table is DATETIME2(0), it is a smaller precision.

We don’t want to compare a higher precision parameter to a lower precision column because that’ll make it look at every single row.

What I need to specify is…

I want a DATETIME2 column that is precision zero. We’ve got the exact same query here, I’ve just specified my precision, and now when I execute it, it’s a lot faster than seven seconds.

I’m going to click on my select operator, going to go into my Query Time Stats, and I can see from my Query Time Stats: okay we were down to half a second, with 700 milliseconds of CPU time this time. So way better!

A magical scan: a scan with a seek predicate!

When I go over and look at my clustered index scan, it is still technically a clustered index scan, but there’s something really interesting on this clustered index scan. It has a seek predicate on it, as well as a predicate! So it’s a clustered index scan, but with a seek predicate. It’s magic, right? We’ve got a scan that is also a seek!

If I look into what this seek predicate is– and I just popped up the details on that seek predicate– It looks kind of crazy, but there is some magic to this craziness.

The seek predicate is on PtnId

I do not have a column named PtnId. What it’s trying to say is: I am doing a seek predicate to do partition elimination. When you see these predicates that are saying partition id Scalar Operator in here, these are all my boundary points, and it’s saying: I am trying to eliminate partitions.

Well how did it work? This is an actual plan, so I can go on up and say: what is the partition count, and which ones did you access?

We accessed six partitions total. Those were partitions 51 to partition 56. We did get partition elimination now that we used the appropriate data type on our parameter, and didn’t have a parameter that was a more specific datatype– a DATETIME2 precision 7.

In this case we said: no, we want it to be a DATETIME2 precision 0, don’t implicitly convert it to whatever you feel like, which doesn’t happen to be DATETIME2 precision 0.

This is when we had the luxury of looking at, of course, the actual execution plans.

I have the luxury of being able to run the query with the magical execution plan button on. You can’t always do that in the real world.

In the real world, maybe my query would modify data and I can’t just run that. Or maybe it would just take so long, or use so many resources that I can’t run it. There’s lots of reasons I might not be able to just get an actual plan.

We might be looking at execution plans in the query plan cache.

Let’s see what these look like in the plan cache

I’m looking at sys.dm.exec_query_stats and its little friends, the text of the SQL DMV and the query plan DMV. And I’m saying: okay, well I want to see the plans in the query execution cache that have this text in the query that I just copied out from the queries in question.

So I’m looking at things like: how much CPU time did they use, and what is their execution plan?

We can easily tell the fast query from the slow query. Our first query here had 7.1 elapsed seconds, the fast query took half a second. So we can easily tell who’s who.

This top query who didn’t get partition elimination had to do 488,000 logical reads. It did also have to do a bunch of physical reads, so that is part of the reason why it was slower, but it’s not the whole reason why it was slower– our faster query only had to do 12,000 logical reads because of that partition elimination.

Looking at our slow plan who wasn’t able to do partition elimination…

I just clicked on the XML and opened the plan. You might say: wait where’s the plan? Well, this is one of those little things Management Studio can do sometimes. It gave this ‘assign’ operator a huge amount of room. If you go to the bar on the right and drag it down, you can then see the second part of the plan. There’s just some weird spacing issues in Management Studio where sometimes it’ll give you a really wide plan. Look for the bar and click down.

This one who didn’t get partition elimination, this is the cached plan. When I look at the properties of the clustered index scan, I don’t have any information about which partitions that were accessed, that’s only available in actual plans. And the plan might be reused! It might do different things on different runs. We don’t have {Partitions Accessed} because that actual partition information, you only get actual plan {info} in actual plans. This is a cached plan.

But there is more we can look at.

Let’s go down and look at the predicate.

There’s no seek predicate anywhere

You’ve got to know what to look for that is missing! There’s no seek predicate, and when we look at what the predicate is– always remember to think of a predicate as a hidden filter– it’s nothing about partitions. It just says: I’m applying this filter to every row I look at. So there’s no indication anywhere that we got partition elimination, but yes, if you see here, it certainly knew that it was partitioned.

We can tell by the absence of that seek predicate on the partition id, hey there’s no sign of partition elimination happening.

Now, the query who only took half a second

If we open up the plan, it does something similar with the spacing. This time I just used the scroll mouse to scroll down, and I click on the clustered index scan.

Look at the properties– again this is a cached plan so I don’t have any Actual Partition Count.

I can see there is a seek predicate here

I’m going to pop up my seek predicate, and I can see: oh okay there was a seek predicate, you were trying to do partition elimination here, and then based on things I can look at useful information from those related queries in the plan cache and see: okay on average you’re doing about 12,000 logical reads.

If I want to do the math– okay if you’re reading this many pages on average, on average about how many partitions are you using? I could probably figure that out by looking at information about how many pages are on average in each partition. I could make a guess, right?

So, very, very interesting.

I need to be a little crafty when I’m looking at those cached plans.

Just for fun, what does it look like in Query Store?

If we’re lucky enough to be on 2016+ and have Query Store, we can look them up in there too.

I have the built-in Top Resource Consumers report open, and you may prefer to query your Query Store data, that’s totally fine. Just for ease of use I’m going to refresh this report. I have this also in the view that shows them in a grid format with details.

The default is this graph format, I find that if you are using the built-in report. I think this is a nicer view. I can scroll down and say: okay where does it look like my query is?

Well, here’s two queries. One of them– and I love how it makes it real easy to see here– one of them used DATETIME2 precision 0 and the other used DATETIME2 precision 7. The one who used DATETIME2 precision 0, well gee it only used 706 milliseconds of CPU time.

Whereas the one who used DATETIME2 precision 7, well it used a lot more. Once I have this highlighted, it will say: I have one execution plan for this with Plan Id 62, and I see the plan down here. If I click on that, just like in other plans, I can have my properties window open, I can drag things around here.

I don’t – this like the cached plan. I do not have that actual info here, but I can scroll down, and this was the fast one that used DATETIME2 precision 0. I can see that there was a seek predicate. Let’s see, there we go, I’ll find the magic button to pop it up – and sure enough we can see: hey you had the seek predicate that was doing the partition elimination.

Very cool, that similarly to the cached plan, I can see the seek predicate in there.

It has the benefit of over the cached plan of: since it’s in Query Store it won’t just disappear if your SQL Server restarts or if there’s memory pressure.

Depending on my Query Store grooming settings, it may go away, right? But I control those more easily.

I can also go to my query that has the DATETIME2 precision 7– and I love how it’s so easy to see the data type there, click on this and see: oh you had a predicate. You didn’t have any seek predicates on partition id.

So, there is a way– once you get used to this– to read those in the plan, it’s just not necessarily as easy to see as when you’re looking at the actual plan.

If you do have the ability to generate an actual plan, I do think it makes your life easier

There’s a lot of detail you can get!

But even if you can’t get an actual plan, once you know what to look for in either the cached plan or the Query Store plan, you can get a long ways in figuring out hey how many partitions did you actually touch.