{"id":203,"date":"2024-12-31T08:00:00","date_gmt":"2024-12-31T13:00:00","guid":{"rendered":"https:\/\/www.sqltabletalk.com\/?p=203"},"modified":"2024-12-30T22:06:02","modified_gmt":"2024-12-31T03:06:02","slug":"understanding-and-resolving-tempdb-contention","status":"publish","type":"post","link":"https:\/\/www.sqltabletalk.com\/?p=203","title":{"rendered":"Understanding and Resolving TempDB Contention in SQL Server"},"content":{"rendered":"

Introduction<\/h1>\n

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\u2014especially leveraging In-Memory OLTP and memory-optimized table variables.<\/p>\n

Common TempDB Contention Issues<\/h2>\n

PFS and SGAM Contention<\/h3>\n

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\u2014particularly the system tables in TempDB.<\/p>\n

For more information on troubleshooting PFS\/SGAM contention in TempDB, see Jonathan Kehayias\u2019s blog post: Optimizing tempdb configuration with SQL Server 2012 Extended Events<\/a><\/p>\n

Understanding System Table Contention<\/h2>\n

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<\/code> under hexadecimal names.<\/p>\n

If you create a temporary table that already exists in cache, SQL Server performs a simple \u201crename\u201d 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.<\/p>\n

Example: sys.sysschobjs<\/h3>\n

sys.sysschobjs<\/code> is the base table for sys.objects<\/code> in TempDB. You can quickly examine its structure using sp_help<\/code>:<\/p>\n

USE tempdb;\nGO\nEXEC sp_help 'sys.sysschobjs';\nGO<\/code><\/pre>\n
Among its indexes, one is a nonclustered index on a TINYINT<\/code> column named nsclass<\/code>, and another starts with the name<\/code> column. Under high concurrency, these indexes can become hot spots for latch contention.<\/p>\n
Demonstrating Latch Contention<\/h2>\n
You can reproduce this contention using the OSTRESS<\/strong> command-line tool (part of the RML Utilities). First, set up a simple test scenario:<\/p>\n
    \n
  1. Create a test database<\/strong>:\n
    USE master;\nGO\nCREATE DATABASE ContentionTest;\nGO<\/code><\/pre>\n<\/li>\n
  2. Add a stored procedure<\/strong> that creates a temporary table:\n
    USE ContentionTest;\nGO\n\nCREATE PROCEDURE dbo.Test\nAS\n    CREATE TABLE #Test (\n        Id INT,\n        Col1 NVARCHAR(128)\n    );\n    INSERT INTO #Test\n    SELECT 1, 'Test';<\/code><\/pre>\n<\/li>\n
  3. Run OSTRESS<\/strong> to hammer your instance with high concurrency:\n
    ostress -Q\"EXEC ContentionTest.dbo.Test;\" -n500 -r500 -S\"localhost\"<\/code><\/pre>\n