Performance Improvements in SQL Server 2019

on YouTube ↗49m

" summary

SQL Server 2019 introduces Accelerated Database Recovery and a persistent version store that cut rollback time dramatically — a 100k-row delete goes from ~31 seconds to 11 seconds, and rollback from ~31 seconds to ~6 milliseconds in the demo — while log usage drops by over two thirds. Tempdb metadata can move to in-memory tempdb metadata to eliminate page latch contention (a 20-connection ostress run took 1m50s without it). Support for persistent log buffer / PMEM enables hybrid buffer pool and Linux-only enlightened I/O; Intel Optane 512GB sticks cost ~\$19k.

AI-written summary · about

" chapters (8)

1. 0:00Start
2. 4:00TRANSACTION LOG ARCHITECTURE
3. 14:44ACCELERATED DATABASE RECOVERY - NEW CONCEPTS
4. 18:14BENEFITS OF ADR
5. 28:01THE OLD CONTENTION SOLUTION
6. 41:29ADVANTAGES OF PERSISTENT LOG BUFFER CACHE
7. 42:11HYBRID BUFFER POOL
8. 46:37RESOURCES

" related talks

• 54mSoaring to New Heights with Accelerated Database RecoveryJohn Morehouse · SQLBits 2022
• 54mSoaring to New Heights with Accelerated Database RecoveryJohn Morehouse · SQLBits 2019
• 50mSQL Server 2022 Performance EnhancementsOla Hallengren · SQLBits 2024
• 50mSQL Server 2022 Performance EnhancementsOla Hallengren · SQLBits 2022
• 51mNext Level Scalability on SQL Server 2022Ajay Jagannathan, David Pless · SQLBits 2023
• 49mHidden Gems in SQL Server 2022 Database EngineAjay Jagannathan, Dimitri Furman · SQLBits 2024
• 49mHidden Gems in SQL Server 2022 Database EngineAjay Jagannathan, Dimitri Furman · SQLBits 2022
• 54mSoaring to New Heights with Accelerated Database RecoveryJohn Morehouse · SQLBits 2024