Monitor TempDB Contention

Install metric...

Metrics install automatically if you have SQL Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, SQL Monitor, you can instantly install and run this metric on your servers.

Measures the total duration of latch waits on the tempdb database, in a one-minute time interval.

First, create and start for the following Extended Events session on the monitored SQL Server instance:

The goal is to get the total duration of latch waits, in the last minute, so this session collects all the event data, without any filter except for the database. Its designed to be as lightweight as possible in other regards, collecting only one event, and no additional data.

It might be useful to modify the event session to collect additional event data (“actions”) such as the session_id and sql_text associated with the session that was blocked. However, you’ll need to test it out, gauge how much data it collects over a period, and how much overhead it will cause to have it running continuously.

For further details see: Monitoring TempDB Contention using Extended Events and SQL Monitor.

Metric definition

Name

MonitorTempDBContention

Description

Measures the total duration of latch waits on tempdb, over a period (one-minute time intervals, in this example). Collected over time, this will establish a baseline, showing the typical profile for tempdb latching for your workload, and allow you to spot abnormalities that indicate contention.

The T-SQL query that will collect data

Instances to collect from

Default

Databases to collect from

tempdb

Collection frequency

1 min

Use collected or calculated values

Leave the Use a calculated rate of change between collections check box unchecked

Metric collection

Enabled

Alert definition

The appropriate alert thresholds are entirely dependent on your system, and you should only enable them after establishing a baseline for your working instance.