Transcript

A question about changing partition granularity

Question: Partitioning by a date column is complex because new dates need to be in a next partition and you need to make the switch between the next partition and a month year partition. Do you know of a way to simplify or automate this process?

Hmmm. My understanding of this question is… I’ll just come up with some example. What if I normally in one table have partitions that are based like on individual days, but then at one point, I want to take these days, and I want to instead move them into a partition based on month? I want to change the– this is basically changing the boundary points.

When we create partition functions, SQL Server, it understands them as individual boundaries. And you have to specify them as individual boundaries. You don’t say, you know, create, you know, partition by month. You say: here are the specific boundary points I want to have.

If at any point I’m going to change those boundary points, and I’m either going to remove one when there’s data there, or I’m going to add one when there’s data there, things get messy. Things get really messy, and I may have a lot of logged operations and it is painful.

So it’s really important to determine the granularity that I want a partition at, and if I have any way I can avoid changing that, like I would actually say, okay, is there a real reason that I need to not partition by day at a certain point? Like, can’t I just write my code to say, I want to query a month’s worth of days, because, yeah, it’s hard for reasons to split and merge partitions when there’s data in it, because we are changing the boundary points. So no, I don’t know of a way to make that easier. If I am going to do something like merge a boundary point, typically the best way I would want to do that, is like, switch the data out of the big table, change around the partition key, and then sort of like bulk insert back in. It’s not pretty.

Lizzie says, “Can partitioning large tables help with insert performance?”

“I know you can use hashed partitioning functions to spread the inserts around, but can you get partition elimination with a hashed partition function?” Ahh, so great question.

Lizzie’s asking about a scenario that is often used due to latch contention in SQL Servers. So if I have a lot, not one big bulk insert, but let’s say I’ve got inserts coming from a lot of different threads into the same table, and I’ve got something like an ever-increasing identity, maybe keyword ID, right? Ever-increasing, and let’s say I’ve got a ton of different application servers, and they’re all like, keywords are coming in one by one.

In the traditional situation of rowstore indexes, I can run into latch contention in these super high insert rate scenarios, where just getting access to the pages and memory to do the inserts becomes my bottleneck. And when I look at my wait stats, I have latch waits out the wazoo.

One of the classic, I mean there are many different recommendations for fixing that. One of the recommendations for fixing that is actually you could potentially use a unique identifier, otherwise known as a GUID. Hated by DBAs! You could use that as your clustering key, which means you get inserts randomly into the table, right? That’s one way to fix it.

Another way to fix it is I could partition the table in a way such that I’m directing different inserts into different partitions so that they aren’t all going into one place in one partition with latch contention, and I can round-robin them. If I do that, all of my queries that are selecting from the table, yes you can do it, Lizzie, but you have to be really clever writing your code, so that your queries coming in that are all on whatever column you’re partitioning on, figuring out, okay, here’s exactly how I, you know, specify the keys so I get partition elimination. So you’ve got to kind of be a little clever, but you can do it.

We do have a new option for that case, though. For that case of latch contention on inserts now we have an additional option to test of In-Memory OLTP, which was designed for, like, one of the big reasons that In-Memory OTLP came about was because a lot of those options to fix latch contention kind of had problems with them. Like, they’re not really easy to do, and we got to do a lot of complicated shenanigans with them. So In-Memory OLTP is actually, I would evaluate that one first these days, and be like, okay, well this was actually written to help with our scenario, is it good for us? Because there are certain limitations with it, it may not be a great fit.

But yes, partitioning is one of the family of options for latch contention.

We had a comment:, “Clustered GUIDs, aaaaaaa!” Yeah, it gets nasty when we hit latch contention. It gets nasty. We have to start considering all these things, where we’re like, oh, now I have a 16 byte GUID, and things can go anywhere into my table. Fun times, fun times.

More about partition granularity

If we partition a table by a date column, creating a partition for every month in a year, for example, we would need to create some new partitions at some point moving the old data that were in old partitions to a backup or an old partition that usually is on slower disk on your storage.

Okay, okay, I’m understanding more about this now. I’ve talked about the coolness of switching in and switching out. And I mentioned that we have to be on the same filegroup. What if I don’t want to be on the same filegroup anymore? What if i want that partition of data to be either on a different filegroup because it’s on slower storage, that’s cheaper. Or, maybe I want it in a different database. Maybe I want it on a different server.

We have to do all of that ourself. I am sorry. Years ago, I was convinced, so we had a situation, where we had, you know, a production server with partitions, and we would take data out of the table and then we would move it into an archive database. And our archive database was on a totally different instance.

I was like, there has to be a way to make this easier. Right? There has to be a way. And I kept trying to find a way to backup a filegroup from our live database with just, you know, just that data from that partition in it, and then restore it to my archive database. Just restore a filegroup from one database to a different database.

No, there’s no supported way to do that. I’m going to use the word supported here, there are ways to force it to work. There are ways that mean that if you ever seek help for that database, people will be like, what in the world have you done to that database? No wonder it’s acting weird! So I wouldn’t, it’s not a way I would use. But yeah, there is no easy way of.. We have to bulk export, you know, we can take all the data out and the bulk import elsewhere but there isn’t a magic switch for that. I mean, I would, whenever we could use bulk operations, use bulk operations, but yeah, you already know that. I’m afraid I don’t have that magic. I was so convinced that this had to work, but yeah, not so much, not so much.