Hidden Gems in SQL Server 2022 Database Engine

on YouTube ↗49m

" summary

Demo-heavy tour of small fixes that deliver big wins: handsfree tempdb removes PFS/GAM/system-table latch contention so a single tempdb file runs with no latches (demo throughput ~2x, workload time falls from 28s → 12s), plus lazy log flush. XML compression cuts XML size ~50% and halves logical reads (CPU up but wall time similar). low-priority async stats prevents blocking chains during auto-updates. Auto-drop stats, T-SQL helpers (generate_series, date_bucket, greatest/least) and faster log growth zeroing (17s → 7s; VLFs 51 → 26) round out practical fixes.

AI-written summary · about

" chapters (13)

1. 0:00SQL Server 2022 hidden gems
2. 1:54Tempdb improvements
3. 8:15Tempdb latch contention reduction
4. 12:53XML compression in SQL Server 2022
5. 15:03XML compression savings and performance
6. 18:36Asynchronous statistics updates
7. 23:49Low-priority statistics updates
8. 29:14Statistics auto-drop for schema changes
9. 31:50T-SQL improvements in SQL Server 2022
10. 33:14IS DISTINCT FROM and T-SQL functions
11. 38:31Performance engineering improvements
12. 43:50Instant file initialization for log files
13. 47:44Additional hidden gems and closing

" related talks

• 49mHidden Gems in SQL Server 2022 Database EngineAjay Jagannathan, Dimitri Furman · SQLBits 2022
• 48mSQL Server 2022 hidden gemsBob Ward · SQLBits 2023
• 51mNext Level Scalability on SQL Server 2022Ajay Jagannathan, David Pless · SQLBits 2023
• 52mWriting T-SQL code for the engine, not for you "more demos" by popular demandCLÁUDIO SILVA · SQLBits 2022
• 49mPerformance Improvements in SQL Server 2019Frank Gill · SQL Server Virtual Conference 2021
• 50mSQL Server 2022 Performance EnhancementsOla Hallengren · SQLBits 2022
• 52mWriting T-SQL code for the engine, not for you "more demos" by popular demandCLÁUDIO SILVA · SQLBits 2019
• 43mPerformance Tuning, Getting the Biggest Bang for Your BuckMonica Rathbun · SQL Server Virtual Conference 2021