Next Level Scalability on SQL Server 2022
summary
SQL Server 2022 removes major tempdb choke points by turning GAM/SGAM update latches into shared latches so high-concurrency workloads stop producing the giant latch wall seen on 2019; demos compare 2019 vs 2022 instances with a streaming Power BI workload. Buffer pool scans become parallel on many cores (2 TB machine shows dramatic elapsed-time drops). ordered clustered columnstore cuts segments read from ~102 to 4 and IO by ~10x on a 100M-row test. transaction ID locking plus lock-after-qualification drops locks from millions to single-digit and memory use from ~900 MB to ~1.6 MB. Diagnostics: sys.dm_db_page_info, page rescracker, extended events and DMVs.
AI-written summary · about
chapters (9)
- 0:00Introduction to SQL Server 2022 scalability
- 1:23Scalability challenges and history
- 5:14TempDB bottlenecks and best practices
- 17:09TempDB improvements in SQL Server 2022
- 22:04Buffer pool parallel scan in 2022
- 26:14Clustered columnstore index enhancements
- 31:26Optimistic locking for concurrency
- 40:58Additional engine improvements
- 44:29Q&A session
related talks
50mSQL Server 2022 Performance Enhancements- 50mSQL Server 2022 Performance Enhancements
49mHidden Gems in SQL Server 2022 Database Engine- 49mHidden Gems in SQL Server 2022 Database Engine
49mPerformance Improvements in SQL Server 2019
50mTime Series with SQL Server 2022
48mMastering TempDB
51mMaking the SQL Query Processor Work for you