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

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