Execution Plans: Partitioned Tables and Columnstore Indexes (1 hour 30 minutes)

Execution plans are incredibly helpful when it comes to tuning queries using partitioned indexes and columnstore indexes – but when you look closely, you’ll notice that some things are very weird!

Try the course quiz anytime.

Learn how to see:

  • How many partitions have been accessed by a query
    • In actual plans
    • What indications you can find for partition elimination in estimated/cached plans
  • When SQL Server will ‘lie’ about the partition count– and what that means
  • When “0 rows” is really more than 0 rows
  • The basics of batch mode vs row mode operators in execution plans
  • When rowgroup elimination happens, and how this compares to partition elimination

Get the scripts

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

Note: following this recording, Microsoft introduced batch mode on rowstore as part of SQL Server 2019.

Lessons

Partitioning and columnstore concepts (10 minutes)

Disk based rowstore: actual plans, cached plans, and Query Store (21 minutes)

Columnstore: 0 partitions accessed- but rowgroup elimination! (15 minutes)

More fun with rowgroup elimination and predicate pushdown (12 minutes)

When doing more is less work: comparing Query Time Stats for two queries (5 minutes)

When you can get batch mode operators, and an example of their power (14 minutes)

Batch mode hacks, and what does the future hold? (9 minutes)

Quiz on partitioned tables and columnstore indexes

Concepts to remember, and thank you! (4 minutes)