Banishing Slow Queries with Intelligent Query Processing

on YouTube ↗53m

" summary

Overview of SQL Server 2019 and 2022 Intelligent Query Processing features that actually change plans with zero code edits: enabling table variable deferred compilation moves an estimated rowcount from 1 to 228,000; lightweight query profiling and Query Store (or trace flag 7412 pre-2019) collect runtime metrics; memory grant feedback reduces excessive grants after one execution; parameter sensitive plan creates a dispatcher plus up to three variant plans; degree-of-parallelism feedback auto-tunes DOP (example: 16 → 12 → 8 → 2 → back to 4); memory grant and DOP feedback require Enterprise.

AI-written summary · about

" chapters (9)

1. 0:00Start
2. 1:34Denny Cherry & Associates
3. 4:01CHALLENGE ACCEPTED
4. 6:10SQL Server 2022 IQP
5. 11:29Table Variables Issues
6. 12:09Table Variable Deferred Compilation
7. 15:07Memory Grant Feedback
8. 19:34Parameter Sensitive Plan
9. 28:45DOP Feedback Architecture

" related talks

• 53mBanishing Slow Queries with Intelligent Query ProcessingJohn Morehouse · SQLBits 2019
• 22mEmpowering SQL: Mastering Intelligent Query Processing in 20 MinutesHaider Raza, Manish Kumar · SQLBits 2022
• 53mBanishing Slow Queries with Intelligent Query ProcessingJohn Morehouse · SQLBits 2022
• 44mImproving Performance with Intelligent Query ProcessingJohn Morehouse · SQL Server Virtual Conference 2021
• 22mEmpowering SQL: Mastering Intelligent Query Processing in 20 MinutesHaider Raza, Manish Kumar · SQLBits 2019
• 22mEmpowering SQL: Mastering Intelligent Query Processing in 20 MinutesHaider Raza, Manish Kumar · SQLBits 2024
• 51mMaking the SQL Query Processor Work for youJoe Sack, Ajay Jagannathan, Derek Wilson · SQLBits 2022
• 51mMaking the SQL Query Processor Work for youJoe Sack, Ajay Jagannathan, Derek Wilson · SQLBits 2024