Built-in Intelligence with Azure SQL and SQL Server 2022

on YouTube ↗50m

" summary

Covers practical features in Azure SQL/SQL Server 2022: query store hints let DBAs persist plan hints without changing code and demo shows using OPTION(RECOMPILE) to fix a table-scan vs bookmark lookup problem; parameter sensitive plan optimization allows multiple plans (up to 200 stored, 27 in cache) for different parameter cardinalities; memory-grant percentile persistence smooths grants; DOP feedback ladders down (never below 2) to reduce wasted parallelism; optimized plan forcing replays compilation rules; approximate percentile cuts a 6.3s query to ~1.4s.

AI-written summary · about

" chapters (24)

1. 0:00Intro
2. 3:08Query Store - Custom capture policies
3. 3:59What are Query hints?
4. 6:26Applying Query hints Today
5. 7:40Introducing Query Store hints
6. 9:09Using Query Store hints
7. 18:06Database Compatibility Level Family
8. 19:42Intelligent Query Processing Feature Family
9. 21:46Parameter-sensitive plan problem
10. 23:30Common Parameter-sensitive plan workarounds
11. 24:57Parameter Sensitivity Plan (PSP) optimization
12. 26:03Boundary value selection (Example of Real Estate agents portfolio)
13. 27:23Feedback Workflow
14. 28:25Memory Grant Feedback (MGF)
15. 29:09With MGF Percentile Grant
16. 30:36Parallelism today
17. 36:41Cardinality Estimation today
18. 37:43What is CE Feedback?
19. 38:40CE Feedback implementation
20. 39:33CE Feedback Scenarios (High Level) Correlation
21. 40:21Leveraging CE Feedback
22. 42:46Query compilation today
23. 43:55Optimized Plan Forcing using Query Store
24. 46:31Approximate QP: Approximate Percentile

" related talks

• 49mPerformance Features in SQL Server and Azure SQLJavier Villegas · SQL Server Virtual Conference 2021
• 22mEmpowering SQL: Mastering Intelligent Query Processing in 20 MinutesHaider Raza, Manish Kumar · SQLBits 2024
• 53mBanishing Slow Queries with Intelligent Query ProcessingJohn Morehouse · SQLBits 2019
• 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 2022
• 20mElevating SQL Server Performance with Query Store HintsRui Carvalho · SQLBits 2024
• 51mMaking the SQL Query Processor Work for youJoe Sack, Ajay Jagannathan, Derek Wilson · SQLBits 2022
• 20mElevating SQL Server Performance with Query Store HintsRui Carvalho · SQLBits 2022