SQL Server TempDB: The Most Overlooked Bottleneck

TempDB is the SQL Server equivalent of a junk drawer - everyone uses it, nobody monitors it, and eventually it becomes a bottleneck you can't ignore.

Whether it’s poorly configured from the start or getting hammered by bad execution plans, TempDB often becomes the silent killer of performance. The good news? A few targeted changes can make a big impact.

TempDB Configuration:

· File Count: “one file per core” is outdated. Only go past 8 if you have verified PFS or SGAM contention exists

· File Size and Growth: Pre-size your files to avoid autogrow OR ensure Instant File Initialization is enabled

· Trace Flags 1117 & 1118: relevant for SQL Server 2014 and older, not in newer versions

· Placement: Should TempDB be on its own disk? Ideally, yes. Fast disk, Fastest RAID, separate controller/path to the drives

 Identifying TempDB Bottlenecks and Stalls

· Read/Write Latency: Query sys.dm_io_virtual_file_stats to see stats on ms/read and ms/write. Write is usually worse.

· Spills: Look for queries that are spilling out to tempdb due to poorly estimated memory grants

· Monitoring Tools: I use exec sp_blitzcache @Sortorder = 'Spills' for this (part of the First Responder Kit)

 Reducing Traffic TO TempDB

· Avoid Unnecessary Sorts and Spills: Bad execution plans are a TempDB killer, and unneeded sorts make it worse

· Test the Use of CTEs, Table Variables, and #Temp Tables: Test your code with the different types of temp objects, rather than just blindly using the easiest one. Nobody cares for 10 rows. They ALL care about 10 Million rows!

· Version Store Traffic: If you are using RCSI on multiple databases, one transaction left open can blow out tempdb.

Select
   db_name(database_id)
   , reserved_page_count
   , reserved_space_kb/1024/1024 as [GB Used]
From
   sys.dm_tran_version_store_space_usage
Order by
   reserved_space_kb desc

· Cursors: Some (all?) cursors live in tempdb. At some point a cursor gets too be to be efficient and you’ll want to re-code for speed, as well as tempdb space

 TempDB Best Practices and Quick Wins

· Multiple Files with Equal Size: Equal size is key...autogrow all files is on by default since 2016.

· Instant File Initialization: Saves time on growth events.

· Regular Review: TempDB isn't "set it and forget it". Schedule health checks, just like for the rest of your SQL Server

Could Your SQL Server Survive a Failure Without Major Downtime?

 Take the Quiz!

Find out how bullet-proof your DR plan really is!

SQL tidBITs: What’s in your tempdb right now?

-- Determining the amount of free space in tempdb
SELECT SUM(unallocated_extent_page_count) AS [tempdb free pages],
  (SUM(unallocated_extent_page_count)*1.0/128) AS [tempdb free space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by the version store
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
  (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by internal objects
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
  (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by user objects
SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
  (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

Please share with someone that uses table variables for EVERYTHING!