TempDB Contention

Description

Contributed by Spaceman

Checks for tempdb contention. Had an issue recently caused by this, other metrics didn't highlight much of a problem other than high pagelatch waits. Resolved by adding more data files to tempdb in my case (other options are available though! see The Accidental DBA (Day 27 of 30): Troubleshooting: Tempdb Contention - Paul S. Randal)

Metric Definition

To create the metric, click on Options > Custom Metrics and configure similar to the attached screenshot.

Query Text

SELECT COUNT(session_id)
FROM sys.dm_os_waiting_tasks WITH (NOLOCK)
WHERE wait_type LIKE 'PAGE%LATCH_%'
AND resource_description LIKE '2:%'