DESCRIPTION
This alert will calculate the average Procedure Cache Hit Ratio in the last X minutes based on the Ignite Procedure Cache Hit Ratio resource metric data. The number of minutes the alert uses is based on the Execution Interval field.
The example below uses the "Procedure Cache Hit Ratio" 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 = 'Procedure Cache Hit Ratio'" line to "where mn.NAME = 'Memory Utilization'". The other metric names can be retrieved from this query: select * from con_metrics_names_#DBID# -- #DBID# is the ID from select id, name from cond.
ALERT DEFINITION
To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Single Numeric Return. Configure the alert with values similar to these:
Alert Name: Procedure Cache Hit Ratio
Execution Interval: 10 minutes
Notification Text: The following servers are experiencing low Procedure Cache Hit Ratio.
SQL Statement:
select ISNULL(AVG(md.V*1.0),-1) avg_pchr
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 = 'Procedure Cache Hit Ratio'
and md.D >= DATEADD(MI, -#FREQUENCY#, current_timestamp)
Execute Against: Repository
Units: % Procedure Cache Hit Ratio
High Threshold: Min 80, Max 90
Medium Threshold: Min 70, Max 80
