Execution Plans: Partitioned Tables & 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!

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.

Modules

Menu