You’ve been working with Microsoft SQL Server for a couple of years, and you know a little bit about TempDB. You’ve heard that temp tables and table variables have different performance characteristics than regular user database tables, and you’ve heard that you’re supposed to have multiple data files.
You’re wondering what exactly goes on behind the scenes.
This class is for curious folks who want to learn:
- What uses TempDB, and how that affects performance: not just temp tables and table variables, but also triggers, cursors, sorting indexes, workspace spills, the version store, and more
- How to host TempDB depending on how you use it: understanding what goes in the log file, the data files, why the number of data files matter, and whether your particular workload should be on SAN, local storage, or ephemeral storage, and why
- How to monitor it: after you understand what uses it and how to host it, you need to keep tabs on how its performance changes over time using DMVs, Perfmon counters, and the First Responder Kit scripts
This course is 90% demos: the only slides are the introductions at the start of the day, illustrations to support a few topics, and the recap at the end of the day. The rest of the time, we’ll be working in SQL Server Management Studio. Roll up your sleeves and join me!
Your Progress So Far in This Class
This is driven by the mark-as-complete buttons in each module of the class. (Let’s be honest: you’re probably just going to mark them as complete because you’re that kind of student. I feel you.)
- 0 – Prerequisites Before the Class
- 1 – How the Version Store Affects TempDB
- 2 – How Temp Tables Affect TempDB
- 3 – How Table Variables Affect TempDB
- 4 – Temp Tables and Table Variables at Scale: PFS/GAM/SGAM Contention
- 5 – Memory-Optimized Table Variables
- 6 – How Execution Plans Use TempDB
- 7 – Other TempDB Consumers: Cursors, Index Builds, AG Stats
- 8 – How to Provision TempDB on a New Server
- 9 – How to Monitor TempDB