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.