SQL Server 2022 Performance Enhancements
summary
Covers concrete SQL Server 2022 changes: a global redo thread pool turns per-database redo into a server-wide dynamic pool (observed up to 17 threads on a 144‑logical CPU box), tempdb GAM/SGAM now allow concurrent updates eliminating prior contention, buffer pool scans run in parallel (previously caused ~3 minute failover delay), and Accelerated Database Recovery uses a persistent version store for instant rollbacks but can bloat if a long transaction pins versions across databases. Also details parameter sensitive plan (up to 3 equality params), Query Store hints, memory/DOP/cardinality feedback and stack dump compression (CU8).
AI-written summary · about
chapters (13)
- 0:00Introduction to SQL 2022
- 1:41Key performance improvements
- 3:50Availability groups and redo
- 8:26Tempdb and system efficiency
- 10:24Buffer pool scan optimization
- 12:31Accelerated database recovery
- 19:41Stack dump improvements
- 22:43Parameter sensitive optimization
- 30:45Memory grant feedback
- 36:51DOP and estimator feedback
- 40:03Optimizer feedback mechanisms
- 43:27Automatic plan correction
- 45:47Query store hints and conclusion
related talks
50mSQL Server 2022 Performance Enhancements
51mNext Level Scalability on SQL Server 2022
49mPerformance Improvements in SQL Server 2019
49mHidden Gems in SQL Server 2022 Database Engine
51mSQL Server 2022 and the Wheel of Power
47mNext Level Availability on SQL Server 2022
48mSQL Server 2022 hidden gems
50mBuilt-in Intelligence with Azure SQL and SQL Server 2022