Making the SQL Query Processor Work for you
summary
Upgrade to compatibility level 160 to get automatic QP innovations; SQL 2022 turns Query Store on by default for new databases and persists feedback there. Use sp_query_store_set_hints to apply query store hints (e.g., OPTION(MAXDOP)) without changing code, and sp_query_store_force_plan to reduce compile time (demo drops ~1.2s to ~13ms). Feedback features—memory grant feedback, DOP feedback, and C‑feedback—record adjustments in Query Store and survive restarts; Query Store capture on read‑only secondaries is available in preview under a trace flag.
AI-written summary · about
chapters (7)
- 0:00SQL Query Processor intro
- 15:29Compatibility level deep dive
- 23:33Query Store fundamentals
- 33:04Query Store hints & read-only secondaries
- 33:47Intelligent query processing feedback
- 45:04Non-feedback intelligent query features
- 48:38Summary & Q&A
related talks
20mElevating SQL Server Performance with Query Store Hints
51mMaking the SQL Query Processor Work for you
22mA Beginners guide to Query Store
22mEmpowering SQL: Mastering Intelligent Query Processing in 20 Minutes
53mBanishing Slow Queries with Intelligent Query Processing- 53mBanishing Slow Queries with Intelligent Query Processing
50mBuilt-in Intelligence with Azure SQL and SQL Server 2022
48mQuery Store: A Retrospective