Parameter Sensitive Plan Optimization in SQL 2022 ... As Cool as it Sounds?
summary
SQL Server 2022 adds Parameter Sensitive Plan Optimization (PSPO) which, at compat level 160, creates a dispatcher plan plus up to three variants per skewed equality predicate (determined from column histograms) and surfaces them in query_store_query_variant. The engine appends an internal predicate-range hint that cannot be reused manually; plans materialize only after execution for that range. PSPO does not handle range or many dynamic-search cases, can still misestimate within a variant (e.g. 115 vs 900,000 estimated rows), relies on fresh stats, and is not yet in Azure SQL.
AI-written summary · about
chapters (12)
- 0:00Introduction and speaker bios
- 1:51Tribute to Hugo Kornelis
- 5:37Understanding parameter sniffing
- 7:07Demo: Identifying plan skew
- 12:45Overview of PSPO
- 17:11How PSPO works
- 20:46PSPO limitations and scope
- 23:52Demo: PSPO in action
- 29:19Analyzing PSPO in query store
- 40:01Deep dive into edge cases
- 43:47Requirements and best practices
- 47:39Conclusion and wrap-up
related talks
50mBuilt-in Intelligence with Azure SQL and SQL Server 2022
53mBanishing Slow Queries with Intelligent Query Processing- 53mBanishing Slow Queries with Intelligent Query Processing
22mEmpowering SQL: Mastering Intelligent Query Processing in 20 Minutes- 22mEmpowering SQL: Mastering Intelligent Query Processing in 20 Minutes
20mElevating SQL Server Performance with Query Store Hints
51mMaking the SQL Query Processor Work for you
51mSQL Server 2022 and the Wheel of Power