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
Note: following this recording, Microsoft introduced batch mode on rowstore as part of SQL Server 2019.
- 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)