Partitioning recently got even cooler

Get a bird’s eye view of what you’ll learn in this course, and why it’s more relevant than ever.

Transcript

Welcome to Tuning Problem Queries in Table Partitioning. I’m Kendra Little.

It’s cheaper than ever to use table partitioning

Table partitioning is an exciting subject because it recently became a lot more accessible. Before SQL Server 2016 Service Pack 1, table partitioning was an Enterprise only feature.

We got a great surprise when, in this Service Pack, Microsoft said, “Hey, we’re going to make a lot of features that developers only used to be able to use in Enterprise Edition… we’re making these features available in lower editions.”

Now, if you’re running SQL Server 2016 Service Pack 1 or later, you can use table partitioning in Standard Edition, Web Edition, even Express and Local Edition.

It is important to be aware of a few things, though.

Partitioning isn’t magic performance dust

You may have heard or assumed that if you partition a table, everything just gets faster. But that’s not always the case.

Table partitioning, in a way is transparent. If you partition at table, you don’t have to make changes in the code for it to be able to use the table. But if you care about performance, there are likely some places where you’re going to need to tune the TSQL, the indexes, or both.

In this course, I’m going to show you some query patterns where the queries get slower after we partition the table. I’ll show you options to speed up the queries using TSQL, using indexes, and we’ll talk about the trade-offs in each case.

I’m assuming that you already know a lot about what table partitioning is and when you’d use it. We’re not going to cover all the background in this course. We will be touching on both rowstore indexes in SQL Server, as well as columnstore indexes.

It’s going to be a lot of fun. Let’s get going.