Introduction
DPA includes several metrics around TempDB, but when it has grown to a large size, the question that often comes up is "What's consuming my TempDB database?". DPA does not inherently track that information, but we can use a custom alert (that never alerts) to collect this information. When the alert executes, the results are stored in the DPA alert history tables. The attached query will collect the database name, SQL statement, User MB, Internal MB as the message (first column) and return the total amount of space it's using in TempDB.
Query to Show TempDB Results
When you notice TempDB growing, you can use the data collected by this alert to understand the contents better. Within the attached script, this query is at the bottom of the script and also here for reference:
-- modify the first 4 lines as needed
DECLARE @CollectorAlertName varchar(100) = 'Collect TempDB Contents'; -- the name of your alert
DECLARE @StartDate datetime = '06/08/2024 10:00';
DECLARE @EndDate datetime = '06/10/2024 23:59';
--DECLARE @InstanceName varchar(100) = '<instance name from DPA>'; -- uncomment this to run the query for only 1 instance
SELECT d.name AS InstanceName, ahr.parametername AS SQLText, ah.actiondate AS ActionDate, CONVERT(FLOAT, REPLACE(ahr.levelvalue,',','')) AS TempSpaceUsedMB
FROM ignite.con_alert a
INNER JOIN ignite.con_alert_db adb ON adb.alertid = a.id
INNER JOIN ignite.cond d ON d.id = adb.dbid
INNER JOIN ignite.con_alert_history ah ON ah.alertid=a.id
INNER JOIN ignite.con_alert_history_results ahr ON ahr.historyid=ah.historyid
WHERE a.alertname = @CollectorAlertName
AND ahr.levelvalue > 0
--AND d.name = @InstanceName -- uncomment this to run the query for only 1 instance
AND ah.actiondate BETWEEN @StartDate AND @EndDate
ORDER BY d.name, actiondate DESC;
Create the Collector Alert
To create this alert, create a custom alert of type Multiple Numeric and make it look similar to this. The threshold settings of -1 cause this to alert to never fire, but the results are stored in the con_alert tables within the DPA repository:
