Custom Ignite Alert: SQL Server CPU Utilization#LR14338
This custom Ignite alert will calculate the average CPU Utilization in the last 5 minutes based on Ignite 8 CPU metric data that is collected once every 20 seconds. Note: the query below only works with SQL Server repositories.
The example below uses the "CPU Utilization" metric, but a very similar query can be used for other metrics as well. For example, if you wanted to monitor O/S Memory Utilization, you could modify the "where mn.NAME = 'CPU Utilization'" line to "where mn.NAME = 'Memory Utilization'". For a list of all metric names, you can run these queries:
select id, name from cond; -- use the ID for the instance in the query below for <DBID> select name from con_metrics_names_<DBID> order by name; -- substitute the value of name into the alert query
select ISNULL(AVG(md.V*1.0),-1) avg_cpu_5min from CON_METRICS_NAMES_#DBID# mn inner join CON_METRICS_#DBID# m on m.METRIC_NAME_ID = mn.ID inner join CON_METRICS_DETAIL_#DBID# md on m.ID = md.METRICS_ID where mn.NAME = 'CPU Utilization' and md.D >= DATEADD(MI, -#FREQUENCY#, current_timestamp)
Ignite alert definition