Description
The query below will provide detailed metric values for any metric in the instance, e.g. the metrics shown on the Resources tab in DPA>
Query
DECLARE @DBID varchar(100), @MetricName varchar(50), @SQL varchar(1000);
-- change the instance name here, matches the name shown in the Home page
SELECT @DBID = ID FROM ignite.COND WHERE NAME = '<instance name>';
-- get a list of metric names from CON_METRIC_NAMES_XX
SET @MetricName = 'SQL Disk Read Latency';
-- CON_METRICS_DETAIL_XX holds the detailed collections of the metric value
-- CON_METRICS_HOUR_XX hold hourly summaries, and includes V_AVG, V_MIN, V_MAX values for the hour
-- CON_METRICS_TEN_MINUTE_XX holds 10 min summaries, and includes V_AVG, V_MIN, V_MAX values for the 10 min
SET @SQL = 'SELECT mn.NAME AS "Metric Name", md.D as "Timestamp", md.V AS "Metric Value"
FROM ignite.CON_METRICS_DETAIL_'+@DBID+' md
INNER JOIN ignite.CON_METRICS_'+@DBID+' m ON m.ID = md.METRICS_ID
INNER JOIN ignite.CON_METRICS_NAMES_'+@DBID+' mn ON mn.ID = m.METRIC_NAME_ID
-- adjust the timeframe here
WHERE md.D >= DATEADD(hour, -24, CURRENT_TIMESTAMP)
AND mn.NAME = '''+@MetricName+'''
ORDER BY md.D';
EXEC (@SQL);