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:
IF EXISTS --if the session already exists, then delete it. We are assuming you've changed something
SELECT * FROM sys.server_event_sessions
WHERE server_event_sessions.name = 'MonitorTempDBContention'
DROP EVENT SESSION MonitorTempDBContention ON SERVER;
CREATE EVENT SESSION MonitorTempDBContention
ADD EVENT sqlserver.latch_suspend_end
(WHERE([sqlserver].[database_id] = (2)))
ADD TARGET package0.ring_buffer
(MAX_MEMORY = 4096KB,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0KB,
MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF,
STARTUP_STATE = ON);
ALTER EVENT SESSION MonitorTempDBContention ON SERVER STATE = START;
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.Monitor TempDB Contention,
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
Databases to collect from
Use collected or calculated values
Leave the Use a calculated rate of change between collections check box unchecked
The appropriate alert thresholds are entirely dependent on your system, and you should only enable them after establishing a baseline for your working instance.