The Tempdb Contention (ms) alert provides the latch wait time (in milliseconds) for tempdb allocation maps. The three tracked allocation page types are:
- Page free space (PFS)
- Global allocation map (GAM)
- Shared global allocation map (SGAM)
Latch contention of this sort is usually an indication that you need to create more tempdb data files. In some situation, using Trace Flag 1118 may also alleviate tempdb contention. This alert occurs only on instances running SQL Server 2005 or above.