Version 1

    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);