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.