Your SQL Server’s tempdb is a critical system resource that does far more than just store temporary tables. It serves as a vital workspace for internal operations like query processing, data spills, index rebuilds, and row versioning. These processes make it a common source of hidden performance bottlenecks. To guarantee optimal performance, it is essential to understand these functions and mitigate contention by configuring and using it correctly.

“Pay no attention to that man behind the curtain!” – The Wizard of Oz (1939)

A lot of folks are surprised to learn what tempdb is really used for. Obviously, it’s the home for your temporary tables (#temp and ##globaltemp), but its job is much bigger than that. It’s also where SQL Server builds worktables for internal query operations. For instance, take cursors, hash joins, and spools. An execution plan often needs to do some processing before sending data to the next operator. This work happens in tempdb.

When the query optimizer generates a plan, it estimates the amount of memory needed. It does this by looking at your data types and index statistics. But if those stats are stale or just plain wrong, the optimizer can underestimate the required memory. When the query actually runs, it may need more memory than it was granted. In such cases, the extra data must be stored somewhere to finish the task. That “somewhere” is tempdb. This is called a “spill.” It will definitely hurt performance. Writing to disk, even on your fastest drives for tempdb, is magnitudes slower than working in RAM.

Tempdb also handles row versioning. So, if you enable Read Committed Snapshot Isolation (RCSI), be sure you’re monitoring tempdb. Ensure row versions are short-lived. Resolve any long-running transactions quickly to prevent tempdb from growing out of control.

When building or rebuilding indexes, if you use SORT_IN_TEMPDB=ON then this will utilize tempdb to do the activities. This can speed up the index operation. This is especially true if your tempdb is on faster storage. It also significantly reduces the transaction log usage in your user database. However, you must ensure there is enough free space in tempdb. It needs to hold the entire sorted index, which can be massive.

Ever used the INSERTED and DELETED tables inside a trigger? Those tables don’t just magically appear—they’re built in tempdb. During the trigger’s execution, tempdb holds the “before” and “after” versions of the rows. These rows are affected by your INSERT, UPDATE, or DELETE statement. If you have a complex trigger on a heavily used table, this can create a lot of tempdb activity.

Also, utilities like DBCC CHECKDB and DBCC CHECKTABLE use tempdb to hold their intermediate results. They do this during consistency checks.

Finally, you should be aware of a few common tempdb wait types: PFS, SGAM, and GAM. When you see these popping up, it’s a sign of contention. This means multiple CPU cores are attempting to update the same allocation pages. They do this in a tempdb data file concurrently.

The best way to fix this is to add more tempdb data files. A good rule of thumb is to have the same number of files as you have CPU cores. There’s no harm in having several files. Just make sure they all have the same initial size and the same autogrowth settings. This is key because it ensures SQL Server spreads the workload evenly across all the files.

What’s the most surprising thing you’ve ever found causing tempdb growth in your environment? Was it a runaway spill, an online index rebuild, or something completely bizarre? Share your story in the comments below!