0 – Prerequisites Before the Class
Before this class, watch the free How to Think Like the SQL Server Engine class. Optionally, you’ll get the best experience out of the “What uses TempDB” modules if you’ve already completed my Fundamentals of Query Tuning and Fundamentals of Parameter Sniffing classes because we’re going to be looking at execution plans – particularly, confusing plans where row estimates have been reused from someone else’s session.
Have a local SQL Server 2016 or newer. Your server’s power doesn’t really matter – you can run this class’s labs on your local desktop just fine as long as you’ve got enough space for the database. I will show you the differences in SQL Server 2019, but if you’re not using 2019 yet, no worries – you can just watch those portions of the demos.
Download any Stack Overflow database. In the class, I’ll be using the 50GB StackOverflow2013 version, but any version/size will work.
- 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
2 Comments. Leave new
Dude, love what you’re doing for us “mere mortals”. Question: So I listen to you speak on different aspects of SQL Server, and I just want to be certain I’m not misinterpreting what you say about the relative size of in instance when it comes to performance. For example, SQL has to provide for StackOverflow, as well as some personal use and everything in between. So, my question is about your comment on batches/sec. Can I assume if I have a couple of instances with sub 200, SQL can handle without issue. and only when I start getting above 800 or so, need to monitor more closely. I ask, because we are about to open up a portal for COVID registration. Trying to determine when, or if, I should be concerned. Thanks for the help.
Thanks, glad you like it! For the answer on that one, watch the first module of the Mastering Server Tuning class where I talk about how to measure your SQL Server.