Sql-server – TempDB Contention with 24 physical tempdb files on 48 core AMD Opteron box

sql serversql-server-2005

I just received the following notification about an instance of SQL Server 2005. The instance has a core-to-tempdb-file ratio of 2:1 for a total tempdb file count of 24 files. Contention should not be happening – how would I detect the source of this contention? TempDB and all other databases are on SAN storage via 10 GB Ethernet. The SAN has 46 10k SAS drives configured in one RAID-60 array. This array is shared with several VMWare servers and an Exchange server or two.

Notification from Idera SQL Diagnostic Manager:

11/8/2012 10:49:00 PM, Tempdb Contention (ms) on MGSQL01 is Critical.

Tempdb latch contention has been detected on MGSQL01. The total wait time detected is 1782 milliseconds. This is an indication that performance is being impacted by contention on allocation maps in tempdb. If this is a regular problem it may be alleviated by following best practices with respect to tempdb file count, size, and IO subsystem.

PFS Wait Time: 1782 ms GAM Wait Time: 0 ms SGAM Wait Time: 0 ms

Tempdb Contention (ms): Current wait time for tempdb allocation maps (GAM, SGAM, and PFS), in milliseconds. This alert may only be raised on instances running SQL 2005 or above.

Best Answer

Trace flag 1118 forces uniform extent allocations instead of mixed page allocations. The trace flag is commonly used to assist in TEMPDB scalability by avoiding SGAM and other allocation contention points. If you have SQL Server 2008 or SQL Server 2005 and the fix applied and are still encountering TEMPDB contention consider trace flag 1118 for further assistance in resolving the contention.