Introduction

TempDB contention is a common challenge in SQL Server when running highly concurrent workloads. In this post, we will explore why it occurs, how to identify it, and some practical solutions—especially leveraging In-Memory OLTP and memory-optimized table variables.

Common TempDB Contention Issues

PFS and SGAM Contention

One well-known issue arises from page allocation structures, specifically the Page Free Space (PFS) and Shared Global Allocation Map (SGAM) pages. A common solution is to add multiple TempDB data files, which can reduce contention on these allocation pages. However, even with multiple data files, you may still face contention in other areas—particularly the system tables in TempDB.

For more information on troubleshooting PFS/SGAM contention in TempDB, see Jonathan Kehayias’s blog post: Optimizing tempdb configuration with SQL Server 2012 Extended Events

Understanding System Table Contention

When you create a temporary table, SQL Server must insert metadata into various system tables in TempDB (similar to creating a permanent table in a user database). Often, the temporary table definition is cached for reuse. These cached objects show up in tempdb.sys.tables under hexadecimal names.

If you create a temporary table that already exists in cache, SQL Server performs a simple “rename” operation instead of inserting new entries into the system tables. However, when creating or dropping temporary tables at a very high rate, you can see contention on these system objects.

Example: sys.sysschobjs

sys.sysschobjs is the base table for sys.objects in TempDB. You can quickly examine its structure using sp_help:

USE tempdb;
GO
EXEC sp_help 'sys.sysschobjs';
GO

Among its indexes, one is a nonclustered index on a TINYINT column named nsclass, and another starts with the name column. Under high concurrency, these indexes can become hot spots for latch contention.

Demonstrating Latch Contention

You can reproduce this contention using the OSTRESS command-line tool (part of the RML Utilities). First, set up a simple test scenario:

1. Create a test database:

   USE master;
   GO
   CREATE DATABASE ContentionTest;
   GO

2. Add a stored procedure that creates a temporary table:

   USE ContentionTest;
   GO
   
   CREATE PROCEDURE dbo.Test
   AS
       CREATE TABLE #Test (
           Id INT,
           Col1 NVARCHAR(128)
       );
       INSERT INTO #Test
       SELECT 1, 'Test';

3. Run OSTRESS to hammer your instance with high concurrency:

   ostress -Q"EXEC ContentionTest.dbo.Test;" -n500 -r500 -S"localhost"

   • -n500 = number of threads
   • -r500 = each thread runs the query 500 times
   • -S"localhost" = your SQL Server instance name

   
   Warning: Do not run this test on a production server. High thread usage can exhaust resources and make the instance unresponsive.

4. Check for contention while the test runs:

   USE master;
   GO
   
   SELECT  
       es.session_id,
       es.login_time,
       er.wait_type,
       er.wait_resource,
       er.command,
       DB_NAME(er.database_id) AS dbname
   FROM sys.dm_exec_requests AS er
   JOIN sys.dm_exec_sessions AS es
       ON er.session_id = es.session_id
   WHERE es.is_user_process = 1;

   If you see many sessions waiting on PAGELATCH_EX with a resource like 2:1:14469, this indicates latch contention on page 14469 in file 1 of database 2 (TempDB).

5. Identify what’s on that page using DBCC PAGE:

   DBCC PAGE(2, 1, 14469, 3) WITH TABLERESULTS;

   Look for the rows with Metadata: ObjectId and Metadata: IndexId in the first result set. Suppose you find ObjectId = 34 and IndexId = 2.

6. Confirm the table:

   USE tempdb;
   GO
   
   SELECT name
   FROM sys.objects
   WHERE object_id = 34;

   This might show the object is sys.sysschobjs. Index 2 leads with nsclass, a TINYINT column, which can be a bottleneck due to low selectivity.

You may encounter similar contention in other system objects such as sys.sysobjvalues for temporary table auto-stats.

Practical Solution: In-Memory OLTP

Memory-Optimized Table Variables

SQL Server’s In-Memory OLTP engine (often referred to as Hekaton) allows you to create memory-optimized objects that use optimistic concurrency. A straightforward way to leverage this is with memory-optimized table variables.

1. Enable a memory-optimized filegroup:

   ALTER DATABASE ContentionTest
   ADD FILEGROUP imoltp CONTAINS MEMORY_OPTIMIZED_DATA;
   
   ALTER DATABASE ContentionTest
   ADD FILE (name = 'imoltp01', filename = 'C:\data\imoltp')
   TO FILEGROUP imoltp;

2. Create a memory-optimized table type:

   USE ContentionTest;
   GO
   
   CREATE SCHEMA MemoryOptimized;
   GO
   
   CREATE TYPE MemoryOptimized.IdTable AS TABLE
   (
       Id INT,
       Col1 NVARCHAR(128),
       PRIMARY KEY NONCLUSTERED (Id)
   )
   WITH (MEMORY_OPTIMIZED = ON);
   GO

   I created a schema called ‘MemoryOptimized’ to create my table type under. How you organize your objects is your business, but as you start using memory-optimized objects, I highly recommend placing these objects in their own schema just for the sake of clarity.

3. Use the memory-optimized table type in a procedure:

   CREATE PROCEDURE dbo.Test2
   AS
       DECLARE @Test MemoryOptimized.IdTable;
   
       INSERT INTO @Test
       SELECT 1, 'Test';

4. Rerun your OSTRESS test:

   ostress -Q"EXEC ContentionTest.dbo.Test2;" -n500 -r500 -S"localhost"

   You should see a dramatic reduction in contention, and queries may finish much faster. This is because temporary storage has moved out of TempDB and into a memory-optimized structure in your user database.

Conclusion

TempDB latch contention, especially in highly concurrent environments, can bring your workload to a crawl. While adding TempDB files helps alleviate PFS and SGAM contention, system table contention can remain a bottleneck. Memory-optimized table variables (part of SQL Server’s In-Memory OLTP) are a powerful solution: they move metadata operations out of TempDB and take advantage of lock-free, optimistic concurrency in memory-optimized objects.

References

• Optimizing tempdb configuration with SQL Server 2012 Extended Events (Jonathan Kehayias)
• Improving temp table and table variable performance using memory optimization
• OSTRESS Tool Download