Transcript

Today’s presentation is on Why Table Partitioning Doesn’t Speed Up Query Performance in SQL Server.

I love talking about partitioning: it’s a fantastic technology

I’m not saying that table partitioning isn’t cool. Table partitioning is very cool. It’s just that what’s cool about the feature and where it can really shine is often misunderstood – and it’s something that I absolutely misunderstood when I first heard about partitioning, and when I was figuring out what it can do.

A little metaphor for the misunderstanding about table partitioning is…

There’s these appliances: these electric pressure cookers are all the rage these days. I started hearing about friends who had this electric pressure cooker, how it was cool, you can do so many things with it. you start to hear that you can cook anything in this pressure cooker, and it’s amazing. You can cook cakes in it, you can make vegetables in it. It’s known for cooking beans and meat: things that need lots of cooking time normally in an oven or on a stove top you can do faster in a pressure cooker.

You start seeing people say, “Well, oh no, you can make everything in it.” And it’s just amazing. It practically does your laundry for you.

If you’re like me, you get one of these devices because it sounds so cool

It is going to make everything in my life easier. And you think, oh I’ll try something wacky, I’ll put this in it! And you get out of it a green pile of mush.

If you just throw a soft tender vegetable into a pressure cooker, it’s not necessarily going to go well unless you jut use it to very delicately steam or something like that. If you actually cook it at pressure, it’s not going to go right.

The pressure cooker is an amazing tool, but it is a specialist tool

It is really really great at certain things, and can make those things easier to manage, but you have to be pretty careful with it when it comes to certain things. And when it comes to making things like cakes, they’re going to have a different texture than if you cook them in the oven.

You may have to do some work arounds and like add some extra browning at the end if you want to get a traditional texture, because they don’t have the same environment as an oven has. They are a specialist and they are designed to be a little bit different. So I love my electric pressure cooker but I don’t cook everything in it. I use it in certain situations– and that is really the magic to understanding table partitioning.

Understanding the scenarios in which it really will help you do things and what it helps you do – and why you wouldn’t want to use it necessarily in other situations.

This talk is all slides, I don’t have demos [in the videos – demo scripts covering what we show are available for download].

We’re doing high level talking here, but you can prove everything I’m saying with demo code.

We’re going to talk about…

I’ve heard about partition elimination…

It sounds like table partitioning is magic for performance because we can get the thing called partition elimination with table partitioning! What is that? And why won’t that just speed up all my queries? Partition elimination is cool and it’s useful, but it turns out it’s not magic fairy dust for all of your queries.

We’ll also talk about why table partitioning can actually make the optimizer’s job more difficult in some situations

There are certain query patterns where if you just partition the table, your existing queries may get slower, you may have to do some work arounds to speed them up again. And it’s something you’ll have to do. So you’ll have to go to a little extra effort to get the same result that you had before, in certain situations, not every situation.

Then also those scenarios of: why is this feature so awesome?

What are the things that it makes much faster and easier if it isn’t just going to make my select queries faster?

And that’s what I mean when I’m saying it doesn’t just make my query performance faster. I’m really talking about the data retrieval operations for queries from our users, from our applications. It’s not going to just add lightning bolts to those, sadly.