Why Table Partitioning Does Not Speed Up Query Performance - With One Exception (52 minutes)

Learn why SQL Server’s table partitioning feature won’t make your queries against disk-based rowstore indexes faster– and may even make them slower.

Try the course quiz anytime.

Table partitioning can absolutely be worth implementing, but it may be for different reasons than you think!

In this session, you will learn:

  • What partition elimination really does
  • Why table partitioning makes some queries trickier to optimize
  • Where table partitioning shines - and in which case it can make queries faster

The videos in this session are edited and curated from a live webcast and include some Q&A. This courses uses diagrams to explain these concepts. Scripts for the examples discussed in the videos are included as a download for you to play with if you like to learn hands-on.

Get the scripts

https://github.com/LitKnd/SQLWorkbooks/tree/main/table_partitioning_performance

Lessons

Table Partitioning Is Often Misunderstood - An Analogy (5 minutes)

Partition Elimination (15 minutes)

What if I Don't Have a Predicate on the Partitioning Key? (6 minutes)

Why Partitioning Makes Some Queries Harder to Optimize (4 minutes)

Where Partitioning Shines - and When It DOES Speed Up Queries! (15 minutes)

Final Q&A: Partition Granularity, Insert Speed, Archiving Data to Slower Storage (7 minutes)

Quiz on performance and table partitioning