Hidden Gems in SQL Server 2022 Database Engine
summary
Demo-heavy session that shows concrete fixes and numbers: handsfree tempdb removes most latch contention so a 25-thread stress test falls from 19–28s down to 12s; XML compression halves storage and logical reads in the demo (CPU cost rises) and is enabled by rebuilding an XML-index; async stats gain a low priority stats update mode to prevent compile-block chains; log auto-grow zeroing is avoided when growth ≤64MB, cutting a sample run from 17s to 7s. Uses perfmon, XEvent, stress tool and the estimated compression proc — watch if you want reproducible demos and actionable settings.
AI-written summary · about
chapters (13)
- 0:00SQL Server 2022 hidden gems
- 1:54Tempdb improvements
- 8:15Tempdb latch contention reduction
- 12:53XML compression in SQL Server 2022
- 15:03XML compression savings and performance
- 18:36Asynchronous statistics updates
- 23:49Low-priority statistics updates
- 29:14Statistics auto-drop for schema changes
- 31:50T-SQL improvements in SQL Server 2022
- 33:14IS DISTINCT FROM and T-SQL functions
- 38:31Performance engineering improvements
- 43:50Instant file initialization for log files
- 47:44Additional hidden gems and closing
related talks
49mHidden Gems in SQL Server 2022 Database Engine
48mSQL Server 2022 hidden gems
51mNext Level Scalability on SQL Server 2022
52mWriting T-SQL code for the engine, not for you "more demos" by popular demand- 52mWriting T-SQL code for the engine, not for you "more demos" by popular demand
50mSQL Server 2022 Performance Enhancements
49mPerformance Improvements in SQL Server 2019
53mOptimizing your Azure SQL Database workloads