Understanding TempDB Contention

What It Is

TempDB contention happens when multiple sessions compete for TempDB resources, slowing down SQL Server.

TempDB serves as a workspace where developers temporarily store, manipulate, and process data outside their own databases. It’s also where temp tables are created. Multiple developers may run queries against TempDB simultaneously. While it uses multiple files to handle requests, heavy session activity can still cause delays (“wait times”) when SQL Server creates temp tables.

Why It Matters

• Impacts all queries using temp tables, table variables, or sorting.
• More users = more contention risk.

How to Spot It

• High PAGELATCH_UP or PAGELATCH_EX waits.
• Monitor TempDB usage in SQL Diagnostic Manager.
• Query sys.dm_exec_requests and sys.dm_os_waiting_tasks.

How to Fix or Address It

• Add multiple TempDB data files (1 per CPU core up to 8).
• Enable trace flags 1117 and 1118 (pre-SQL 2016).
• Optimize queries to reduce use of temp tables.

Key Takeaway

Fixing TempDB contention can unlock major performance gains across your SQL Server environment.

How Can Idera Help?

Track and alert on TempDB usage trends using SQL Diagnostic Manager.  For more information or to start a free trial, visit us HERE