Mastering TempDB

on YouTube ↗48m

" summary

Explains which SQL Server features actually land in TempDB (AlwaysOn AGs, RCSI version store, spills, sorts, temp tables) and gives concrete remedies: add matching-size data files one data file per CPU core up to eight, resize together or plan a restart, use checkpoints + DBCC SHRINKFILE then DBCC DROPCLEANBUFFERS/FREEPROCCACHE if needed, and avoid unnecessary DROP TABLE. Notes optimizer fixes: table variable deferred compilation in 2019 removes many spills, and moving to 2022 flatlines engine tempdb usage, so upgrades and specific trace flags (1117/118, per-statement 2453) matter.

AI-written summary · about

" chapters (12)

1. 0:00Introduction to tempdb
2. 2:53What is tempdb
3. 5:16What tempdb holds
4. 10:13Proper configuration
5. 14:32Tempdb in the cloud
6. 17:24Fixing contention
7. 22:18Table variables and tables
8. 28:23SQL Server 2019 improvements
9. 32:34Monitoring space usage
10. 34:27Resizing and shrinking
11. 40:33Spills and statistics
12. 42:36Advanced feedback and closing

" related talks

• 31mBecome an Expert in Managing Your TempdbDeepthi Goguri · Women Data Summit 2021
• 49mHidden Gems in SQL Server 2022 Database EngineAjay Jagannathan, Dimitri Furman · SQLBits 2022
• 51mNext Level Scalability on SQL Server 2022Ajay Jagannathan, David Pless · SQLBits 2023
• 43mPerformance Tuning, Getting the Biggest Bang for Your BuckMonica Rathbun · SQL Server Virtual Conference 2021
• 48mSQL Server 2022 hidden gemsBob Ward · SQLBits 2023
• 50mSQL Server 2022 Performance EnhancementsOla Hallengren · SQLBits 2022
• 57mYour Performance Tuning Check ListMonica Rathbun · Women Data Summit 2021
• 49mPerformance Improvements in SQL Server 2019Frank Gill · SQL Server Virtual Conference 2021