Built-in Intelligence with Azure SQL and SQL Server 2022 - Intelligent Query Processing and Database Features

Explore the built-in intelligence features of Azure SQL and SQL Server 2022 in this 50-minute SQLBits conference talk. Dive into the Intelligent query processing feature family, including improvements to memory grant feedback, degree of parallelism (DOP) feedback, and parameter sensitive plan optimization. Discover how these features enhance business-critical workloads without requiring application code changes. Examine the intelligent database feature family, database compatibility levels, and learn troubleshooting techniques using query store hints. Gain insights from speaker David Pless as he covers topics such as Query Store custom capture policies, applying Query hints, Parameter Sensitivity Plan optimization, Memory Grant Feedback, Cardinality Estimation feedback, and Optimized Plan Forcing using Query Store.

Intro
Query Store - Custom capture policies
What are Query hints?
Applying Query hints Today
Introducing Query Store hints
Using Query Store hints
Database Compatibility Level Family
Intelligent Query Processing Feature Family
Parameter-sensitive plan problem
Common Parameter-sensitive plan workarounds
Parameter Sensitivity Plan (PSP) optimization
Boundary value selection (Example of Real Estate agents portfolio)
Feedback Workflow
Memory Grant Feedback (MGF)
With MGF Percentile Grant
Parallelism today
Cardinality Estimation today
What is CE Feedback?
CE Feedback implementation
CE Feedback Scenarios (High Level) Correlation
Leveraging CE Feedback
Query compilation today
Optimized Plan Forcing using Query Store
Approximate QP: Approximate Percentile