Description
This alert will tell you when an SQL statement has executed more often than it normally does. This example uses a 200% threshold, meaning the SQL has executed twice as many times as normal. The alert definition uses the last 14 days for each SQL statement as the baseline for execution counts per day. It then compares the number of executions today so far with the baseline to derive the percent increase. It also uses summary data so running it more frequently than once an hour will not have benefit.
Alert Definition
To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert with values similar to these:
Alert Name: SQLs with Abnormal Number of Executions
Execution Interval: 1 Hour
Notification Text: The following list of SQL statements had 200% (twice as many) executions today than normal.
SQL Statement:
select today.sqlhash, 100.0 * today.execs / hist.avg_daily_execs pct_increase
from (
select sqlhash, avg(daily_execs) avg_daily_execs
from (
select sqlhash, convert(varchar,datehour,1) day, sum(execs) daily_execs
from con_stats_sum_#DBID#
where period = 'H'
and datehour >= convert(datetime,convert(varchar,current_timestamp-14.0, 1) + ' 00:00:00.000')
and datehour < convert(datetime,convert(varchar,current_timestamp, 1) + ' 00:00:00.000')
group by sqlhash, convert(varchar,datehour,1)) daily
group by sqlhash) hist
inner join
(select sqlhash, sum(execs) execs
from con_stats_sum_#DBID#
where period = 'H'
and datehour >= convert(datetime,convert(varchar,current_timestamp, 1) + ' 00:00:00.000')
group by sqlhash) today on today.sqlhash = hist.sqlhash
where hist.avg_daily_execs > 0
and today.execs / hist.avg_daily_execs > 1
and today.execs >= 10
Execute Against: Repository
Units: % Higher Executions
High Threshold: Min 200, Max empty
Medium Threshold: Min 150, Max 200