Improving Performance with Intelligent Query Processing

on YouTube ↗44m

" summary

Demonstrates specific toggles and metrics: enable lightweight profiling (trace flag 7412 on 2017 or DB scoped configs on 2019 compat level 150) and see real effects — table variable deferred compilation shifts an estimate from 1 row to 228,000 rows and changes plan shape, memory grant feedback shrinks a 731MB grant to ~8.5MB and prevents tempdb spills on subsequent runs, and scalar UDF inlining permits parallelized plans; uses new DMVs (query_plan_stats, in‑flight plans) and shows measurable, working improvements.

AI-written summary · about

" chapters (19)

1. 0:00Introduction
2. 1:20Contact Information
3. 2:25Agenda
4. 3:07What is Intelligent Query Processing
5. 3:43Performance Tuning
6. 4:12Lightweight Query Profiling
7. 6:35Compatibility Level
8. 8:06Lightweight Query Profiling Features
9. 10:16Table Variables
10. 11:07Temporary Tables
11. 11:52Table Variable Compilation
12. 13:37ScalarUFD Inlining
13. 17:32Memory Grant Feedback
14. 20:53Batch Mode on Rowstore
15. 22:54Demo
16. 31:39Inline Scalar Functions
17. 34:35Compatibility Levels
18. 36:29Memory Grants
19. 39:34Batch Mode

" related talks

• 53mBanishing Slow Queries with Intelligent Query ProcessingJohn Morehouse · SQLBits 2023
• 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
• 01h 00m7 Tips to Performance Tuning, Optimization, and EverythingPinal Dave · 24 Hours of PASS: Summit Preview 2015
• 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
• 50mBuilt-in Intelligence with Azure SQL and SQL Server 2022David Pless · SQLBits 2023