Many customers have asked if DPA can alert them when a SQL statement starts executing longer than it has historically. This custom alert will help do that and as written will calculate the following:
- Historical Average - how long has the query historically averaged - uses CON_STATS_SUM_XX DPA table
- Recent Average - how long as the query executed on average recently - uses a combination of CONSW_XX and CONSS tables
- Percent Higher - how much higher is the current average compare to history - returned by the query and used for thresholds
This query returns text as the 1st column and shows the SQL hash, the query text and the stored procedure if applicable. The 2nd column returns the percent higher value, and this is also used in setting thresholds. For example, if you want to be notified of any query that has recently averaged 50% high run times, use 50 as the min threshold for critical levels.
To create this alert, choose Custom from the Manage Alerts page and create a Custom SQL - Multiple Numeric Alert. Past in the attached query and set thresholds accordingly. If you want and medium alert when queries are between 40-50% higher, and a critical alert when queries run more than 50% longer, use thresholds similar to these:
