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, and when SQL Server will lie about the partition count. See when “0 rows” is really more than 0 rows, and learn the basics of batch mode vs row mode operators.
This course contains 90 minutes of videos, with a quiz and downloadable desktop wallpaper.
Course lessons include:
- Partitioning and columnstore concepts (10 minutes)
- Download scripts, slides, and wallpaper
- 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 TIME!
- Concepts to remember, and thank you! (4 minutes)