Problem Queries in Table Partitioning (1 hour 30 minutes)

Some queries may get slower after you partition your tables: the SQL Server optimizer doesn’t always use indexes the same way after those indexes are partitioned into chunks.

But table partitioning is useful in some cases!

Table partitioning is a fantastic tool to help you manage tables with skyrocketing rowcounts. SQL Server 2016 SP1 made table partitioning available in Standard Edition, so you may be planning to add partitioning to your database.

This course teaches you to use execution plans to troubleshoot regressed queries using partitioned tables. You will learn what “non-aligned” indexes are, how to tell how many partitions a query is really using, and see a variety of methods to speed up your queries.

Each video in this course has English captions available, and there is a written transcript in each lesson.

Lessons

What you'll learn in this course (2 minutes)

Get to know the schema and data we'll be testing (4 minutes)

The Aggregator: watch a demo of this problem query (11 minutes)

Let's draw out the problem with this partitioned index (6 minutes)

Tuning the aggregator query (12 minutes)

Partitioned columnstore performance: Standard vs. Enterprise Edition (5 minutes)

The "Max / Min" problem query (8 minutes)

How to speed up the "Max/Min" query (16 minutes)

Why is my query getting blocked? (9 minutes)

How to use the interview questions (2 minutes)

Interview Question #1 (3 minutes)

Interview Question #2 (3 minutes)

Interview Question #3 (2 minutes)

Let's pull together what you've learned (4 minutes)