Notes and links

A note on misleading wording: At one point I note that the partitioning key will be “snuck” into the key for nonclustered indexes, if we do not specify it. This is true for clustered indexes as well, if they aren’t created as unique. (It is often beneficial to have a unique clustered index, so in the moment that case didn’t come to mind and my wording was a bit imprecise.)

For the question where we discuss join order potentially impacting optimization, you can read more about that in this post from Conor Cunningham.

Transcript

First up, partition elimination

This magic, magic partition elimination. We’re going to talk through an example with an example table.

Our example table contains keywords

Imagine we have a system where we’re running some advertising and we let people set up campaigns, maybe to advertise on search engines. We have a table that has KeywordId, which is a BIGINT column, so every keyword in this table get its own unique KeywordId. And we have that as our clustering key at first before we partition the table, a unique clustering key. We also have a CustomerId and then AdvertiserId and CampaignId, because a customer can have multiple advertisers, and advertisers are going to have multiple campaigns. We have a keyword and we have some date columns describing when the data went in the table and when it was modified.

If we want to partition this table…

Let’s say we decide, “I want to partition this table by CustomerId.” I set up this example on purpose this way. Certain tables are more natural to partition than others.

Imagine we had a table that wasn’t just, okay, what are our keywords set up? but, what is historical data on how many clicks each keyword got? We’re going to have a date-time column about when did this click happen. On a table like that, it would be, say, in a warehouse system / in a reporting system, where really, what we’re doing is, keyword clicks, is the nature of our table by that column of when did the click happen.

It would be pretty natural to partition that table by the date-time column because we’re going to have data coming in when we get the data for the clicks and maybe a partitioning by, depending on how much data we collect, how big of a click, you know, search engine we are. Maybe we’re partitioning by day, maybe we’re partitioning by month, maybe we’re partitioning by hour, right? But different columns will be natural.

In this column, in this table, we might want to say, all of our queries against this table tend to be specific to a customer

So we want to partition this table by CustomerId.

That’s kind of tricky because we may have some customers that don’t do a lot of us business with us and some customer who do a huge amount of business with us. So, our partition size may be lumpy. And we can do partitioning this way. It can just be a little bit tricky.

So, let’s say, for example, we’re partitioning on CustomerId. We can make that our partitioning key, but our partitioning key does need to be in our clustered index. We can still have KeywordId as the leading column in our clustered index, that is still allowed, but we need to at least put CustomerId in that key so it needs to either be first, or in this case, second, since we only had one column, and as we say, okay, we want to partition my CustomerId, so we’re going to keep KeywordId as the initial key.

As new keywords get generated, they get ever-increasing KeywordId’s, but we’re going to put CustomerId as the second part of that key and that means that, you know, KeywordId, we don’t have a unique clustered index just on KeywordId anymore, so we have to make that change. All right, okay, so we did that and we’re partitioning by CustomerId.

Now, what our clustered index of our table looks like…

Is a bunch of little trees. Each CustomerId now has their own little clustered index B-Tree. Here, we have, for example, let’s say we have CustomerId 100, 101, 102, and 103. Each customer has their data in their own little tree.

So, as long as we are really always, in our query, saying, “I want a specific CustomerId,” or, “These specific CustomerId’s,” maybe a range, it’s easy to just go to those individual CustomerId’s. That’s kind of awesome, right? Let’s look at some examples of this. Here’s, imagine, some data, in our clustered index, the table itself. And I have drawn these triangles of equal size, of course.

We do have customers of different sizes. Some of these triangles would literally be huge and some would be small. Our clustered index on the table by KeywordId, you know, people may have, customers may have different periods where they just aren’t doing work with us. They just aren’t creating keywords. So within each customer, we know the order of the KeywordId’s.

But overall, in the table, when we’re looking at all of these different customers, they each have their own different range of KeywordIds and we don’t have an overall sort in the whole table. We only are sorting by KeywordId for the individual customers.

We run a query

We say, “I want to select a keyword “where the CustomerId is 101 “and CampaignId equals 500.” Now, CampaignId is not in the clustered index at all, but CustomerId, it’s the second column in the clustered index, but it is our partitioning key. Since it is the partitioning key, we can get partition eliminations. SQL Server says, “Oh, one of the predicates in your query “is the partitioning key of this table.” So, I can go straight to that partition.

Now, CampaignId, it’s not in the clustering key, so if I don’t have a nonclustered index that leads on CampaignId, I am going to have to scan that whole partition and check, okay, for every keyword in this, what campaign are you in? What campaign are you in? What campaign are you in? But at least I didn’t have to scan the entire table. We were able to get partition elimination. So, that’s very, very cool as long as I am specifying the partitioning key as a predicate in my query and I’m not having something like a bad type of implicit conversion happening.

There are things that can screw up partition elimination

I can go straight to that partition and either do a seek or a scan in it. In this case, lets say this is a query that’s run often and I do want it to be fast. I can create a nonclustered index and say, “Okay, I want to create nonclustered index “on CampaignId and I’m even going to include “that keyword column, so that it writes along “in the leaf of that index.” If I use syntax like this to create the index, notice that I haven’t specified to create it on anything. I haven’t said, “Go create it on this file group,” or, “Go create it on this partition scheme.” It will say, it will create this as an aligned nonclustered index. Unless I specify differently, it will create my nonclustered index as partition, so that each customer has their own little chunk in this.

Notice in this nonclustered index, I didn’t specify the partitioning key

In a nonclustered index, it’ll let that go through. It will just secretly put the partitioning key, it will tack it on, like it does.

(As noted at the top of the transcript: it will do this for all aligned non-unique indexes, not just non-clustered.)

I haven’t in this diagram, I haven’t noted that the clustered index key will be secretly put in there too, just for simplicity. The partitioning key will always have to be snuck in, right? And in this case, it is part of our clustered index key.

Now, to step back a little, you can actually partition a heap. It is possible to partition a table without a clustered index. Sometimes folks do that under certain situations where they know, “Hey, I’m always going to want this “partition to be scanned. “I don’t have a useful key that I would want to use “for a look up as a clustered index or anything like that.” It is technically possible to partition a heap. It’s sort of rarely done. Often, the main use I know for that is like if that partition, if the table sitting behind, say an analysis service’s queue that needs to be fully reloaded every night, and we’re going to be scanning the partitions, that might be useful.

So, even though, in our nonclustered index, we didn’t specify the partitioning key, it’s going to end up in the key for our index.

This gets particularly interesting if we want to create unique indexes on a single column…

It’ll be like, if that unique index isn’t on the partitioning key, like, “Hey, I’ve got to problem here. I’ve got to have the partitioning key in the key of your index.” So in this case, if I wanted to create a unique index on CampaignId, which, by the way, doesn’t make any sense in this table, but say I had a table where it did, it would be like, “Oh no, I can’t do that as a partition index, because I have to have CustomerId in there.” I’d have to do a special work around, which we will talk about in a minute. {Non-aligned indexes.}

So, in this case, I want just to be able to find CampaignId quickly and I want to have access to keyword very quickly, so I create this nonclustered index. And I get a partition index with, for every customer, within the customer, the data is sorted by CampaignId, so if I’m going to run this query, and I’m going to say, “Okay, for customer 101, for CampaignId 500, “I want to know all the keywords.” Now, it can not only do partition elimination based on the CustomerId, but I have a nonclustered index that leads on CampaignId so it can seek directly to that index and that is awesome.

A question about defining the partitioning key

I have a question from Diana. “I have a huge table with a primary key that is a composite of transaction ID plus year plus category value. Can I partition on the data on year “if it isn’t a discreet column “in the primary clustered index?” Oh, that is an interesting one.

Okay, there’s a couple parts to this. The way I can think of that you could do that, Diana, is a way I wouldn’t recommend. Like you could potentially, like the partitioning key needs to be a single column. It is allowed to be a computed column. So, in theory, well, not in theory, I mean, you could create a computed column that figures out what the year is in the compound key and creates it as computed column, however, there are many, many problems with having a partition table on a computed column, just with implementation.

I have seen folks who’ve done it, who’ve been writing e-mails like, “I’m getting all these bad query plans and I don’t know why,” and folks look at the query plans and it ends up being due to the fact that it is a computed column. I wouldn’t do it that way.

If you can make some sort of coding change where you do have a discreet column that is not a computed column, yeah, so I would not. The way you can do it, I wouldn’t go. So, yes, you can do it. Folks have had a really bad time with that. Sorry about that. Complexity.

Whenever we add the complexity in with computed columns, even persisted computing columns, there’s a lot of, there’s a lot of problems with that, unfortunately.

Can predicate order break partition elimination?

John says, “We find a lot of instances where the partition key is not used initially. In this case, CampaignId is done first, causing our partitions to be loaded initially, and then CustomerId is used.”

If the example was in this query, the order in which CustomerId and CampaignId are specified in this query, if it was reversed, that should not matter. SQL Server, the order I specify the predicates in, is not generally used. Because it will take all these things out and logically change them. Now, I do, there are a couple exceptions to what I’m saying here.

The exceptions don’t tend to be with the order you specify predicates in. If I have a query that has say, more than seven joins, a large amount of joins in it. Periodically, the only order in which I specify the joins may make a difference in the query plan, simply because the optimizer tries to optimize queries as quickly as possible and it may take different paths in how it explores that optimization tree, based on the order in which I have my joins.

I particularly, I personally, haven’t seen that happen when it comes to the order of predicates in a where clause but if it was something with join order and you have a lot of joins, sometimes I’ve seen the order they are listed make a difference.

For this very simple query, where I just have two predicates in the where clause, I can reverse these and it should not impact my query plan. If it did impact my query plan, in a query like this, I would want to file a bug, “Hey, why is that making a difference? Because it shouldn’t make a difference, the order in which I specify the predicate.”

I’ve shown how partition elimination is really cool, but let’s step back and say…

What if we didn’t partition this table?

I’ve got this query that has predicates on CustomerId and CampaignId. If I am just going to index that and it’s not a partitioned index, I could create an index on keywords and say, “Okay, my two predicates are CustomerId and CampaignId.” And in this case, I have two equality predicates on them. It shouldn’t even matter what order I put the columns in this index, in this case, because they’re both equality predicates. I’m going to create an index on CustomerId and CampaignId and I’m going to include the keyword column. So if I create this as not partitioned, either on a table that isn’t partitioned or if I have a table that is partitioned, I can actually create the index by this by saying, “Create this index,” and then at the end of the create index statement, I say, “Create it on a file group name.” I’m saying, don’t create it on the partition’s key, create this somewhere else.

Now I just have a single big tree. It’s not partitioned and I have cut everything ordered perfectly first by CustomerId then by CampaignId and the leaf of the index also includes the keywords. I’m not getting partition elimination when I run this query, but I don’t need it. I can just seek straight to the rows. Partition elimination is a useful thing, but if I’m not partitioning an index, I can make it really fast just by designing the index well.

And it’s not like this index is bigger because I had to specify CustomerId in the index. No, in the partition index, CustomerId was getting added in there, whether, you know, I specified it or not.

The partition elimination is helping that modified structure, but my performance without the partition index isn’t bad, as long as, I mean, either way, whether the table is partitioned or not, if I’m doing index tuning, I can make it fast. It’s not like partition elimination gave me magic– and there can be some trickiness.