Recommendations to reduce allocation contention in SQL Server tempdb database

You observe severe blocking when the SQL Server is experiencing heavy load. When you examine the Dynamic Management Views [sys.dm_exec_request or sys.dm_os_waiting_tasks], you observe that these requests or tasks are waiting for tempdb resources. You will notice that the wait type and wait resource point to LATCH waits on pages in tempdb. These pages might be of the format 2:1:1, 2:1:3, etc.

When the tempdb database is heavily used, SQL Server may experience contention when it tries to allocate pages. Depending on the degree of contention, this may cause queries and requests that involve tempdb to be unresponsive for short periods of time.

There are several steps you can take to improve the concurrency of tempdb:

Increase the number of data files in tempdb to maximize disk bandwidth and to reduce contention in allocation structures. As a general guideline, create one data file per CPU or core. Each file should be set to the same size. This enables the proportional fill algorithm to distribute the allocation load uniformly with minimal contention.

Consider implementing the best practice recommendations in the Technet article titled Working with tempdb in SQL Server 2005 (http://technet.microsoft.com/en-us/library/cc966545.aspx) .

If the previous steps do not significantly reduce the allocation contention and the contention is on SGAM pages, implement trace flag -T1118. Under this trace flag, SQL Server allocates full extents to each database object, thereby eliminating the contention on SGAM pages. Note that this trace flag affects every database on the instance of SQL Server. For information about how to determine whether the allocation contention is on SGAM pages, see Monitoring contention caused by DML operations subtopic under Working with tempdb in SQL Server 2005 (http://technet.microsoft.com/en-us/library/cc966545.aspx) page on Technet. 

• 328551 (http://support.microsoft.com/default.aspx?scid=kb;en-us;328551) Concurrency enhancements for the tempdb database
• TempDB Monitoring and Troubleshooting: Allocation Bottleneck (http://blogs.msdn.com/sqlserverstorageengine/archive/2009/01/11/tempdb-monitoring-and-troubleshooting-allocation-bottleneck.aspx)
• Managing TempDB in SQL Server: TempDB Configuration (http://blogs.msdn.com/sqlserverstorageengine/archive/2009/01/04/managing-tempdb-in-sql-server-tempdb-configuration.aspx)
• SQL Server TempDB – Number of Files – The Raw Truth (http://blogs.msdn.com/b/psssql/archive/2009/06/04/sql-server-tempdb-number-of-files-the-raw-truth.aspx)
• SQL Server (2005 and 2008) Trace Flag 1118 (-T1118) Usage (http://blogs.msdn.com/b/psssql/archive/2008/12/17/sql-server-2005-and-2008-trace-flag-1118-t1118-usage.aspx)
  

For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, see the following table: