Description
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 Resource Metrics and configure similar to below.
- Database Versions: 9 and above
- Display Name: Tempdb Contention
- Description: Tempdb contention
- Category: Waits
- Units: Waiters
- Metric Type: Single Value
- Frequency: 60 seconds
- Timeout: 30
- Warning/Critical: 5/20 (suggestions only)
SQL Script
SELECT COUNT(session_id)
FROM master.sys.dm_os_waiting_tasks WITH (NOLOCK)
WHERE wait_type LIKE 'PAGE%LATCH_%'
AND resource_description LIKE '2:%'
