This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Average latency

jsql over 5 years ago

I don't find it in DPA but is it possible to get the average disk latency for a selected time period in the Resources charts? Or possibly dig it out of the DPA database itself directly with a query?

I need to have some fixed numbers (not charts) for comparing before and after SAN upgrade improvements. Charts are harder to get an accurate comparison because of the variability.

This could be extended as a feature to any resource chart but all I really need at the moment is latency averages. Min/Max would also be nice.

Thanks!

Top Replies

0 darichar over 5 years ago

Jay, are you wanting to look at a VMware metric (could be specific to a datastore) or standard database metric like SQL Disk Read Latency (averaged across all data files)? If it's the latter, here is a query that should get you started:
DECLARE @DBID varchar(100), @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>';
-- 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 timeframe here
WHERE md.D >= DATEADD(hour, -24, CURRENT_TIMESTAMP)
AND mn.NAME = ''SQL Disk Read Latency''';
EXEC (@SQL);
Cancel
Vote Up +2 Vote Down

Cancel
0 darichar over 5 years ago in reply to darichar

BTW, here is a link to this custom report: Custom Report - Metric Values
Cancel
Vote Up +2 Vote Down

Cancel
0 jsql over 5 years ago in reply to darichar

Excellent. Exactly what I was looking for. I adapted it for MIN/MAX/AVG....next step will be to loop it through all my instance names.
MANY THANKS!!!
DECLARE @DBID varchar(100), @DBNAME varchar(100), @SQL varchar(1000);
-- change the instance name here, matches the name shown in the Home page
SELECT @DBID = ID, @DBNAME = NAME FROM ignite.COND WHERE NAME = '<instance name>';
-- 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
SELECT @DBNAME
SET @SQL =
'SELECT mn.NAME AS "Metric Name", MIN(md.V) AS "MIN", MAX(md.V) AS "MAX", AVG(md.V) AS "AVG"
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 timeframe here
WHERE md.D >= DATEADD(hour, -24, CURRENT_TIMESTAMP)
AND mn.NAME = ''SQL Disk Read Latency''
GROUP BY mn.NAME';
EXEC (@SQL);
SET @SQL =
'SELECT mn.NAME AS "Metric Name", MIN(md.V) AS "MIN", MAX(md.V) AS "MAX", AVG(md.V) AS "AVG"
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 timeframe here
WHERE md.D >= DATEADD(hour, -24, CURRENT_TIMESTAMP)
AND mn.NAME = ''SQL Disk Write Latency''
GROUP BY mn.NAME';
EXEC (@SQL);
Cancel
Vote Up 0 Vote Down

Cancel