Description
This alert will list SQL Statements that have recently executed outside their normal execution time. The previous 14 days are compared when calculating the average execution time for a SQL. The results are reported back in standard deviations with a minimum of 2 deviations to return results.
Warning: This alert is for SQL Server repositories only.
Available Database Instances: All database platforms are supported as this alert executes against the Ignite repository.
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: Abnormal SQL Execution Time
- Execution Interval: 1 Hour
- Notification Text: The following list of SQL statements have been running longer than normal.
- Execute Against: Repository
- Units: Std Deviations from Normal
- Thresholds:
- High: MIN 5, MAX empty
- Medium: MIN 4, MAX 5
- Low: MIN 3, MAX 4
SQL Statement
SELECT 'SQL Statement ' + COALESCE(n.name, convert(VARCHAR, t.sqlhash)) + ' normally averages ' + convert(VARCHAR, cast(round(t.total_avg, 3) AS FLOAT)) + ' but has recently averaged ' + convert(VARCHAR, cast(round(h.last_hour_avg, 3) AS FLOAT)) + ' ---- SQL Text ---- ' + st.st
, cast(round(total_stddev, 1) AS FLOAT) AS total_stddev
FROM (
SELECT TOP 100 sqlhash
, sum(execs) total_execs
, sum(timesecs) total_time
, round(avg(timesecs / execs), 3) total_avg
, stdev(timesecs / execs) total_stddev
FROM ignite.CON_STATS_SUM_#DBID#
WHERE execs > 0
AND datehour > DATEADD(day, - 14, current_timestamp)
GROUP BY sqlhash
ORDER BY total_time DESC
) t
INNER JOIN (
SELECT sqlhash
, sum(execs) last_hour_execs
, avg(timesecs / execs) last_hour_avg
FROM ignite.CON_STATS_SUM_#DBID#
WHERE execs > 0
AND datehour >= dateadd(hour, - 2, current_timestamp)
GROUP BY sqlhash
) h
ON t.sqlhash = h.sqlhash
LEFT OUTER JOIN ignite.CON_SQL_NAME n
ON t.sqlhash = n.HASH
INNER JOIN ignite.CONST_#DBID# st
ON st.h = t.SQLHASH
WHERE h.last_hour_avg > t.total_avg + (2 * t.total_stddev)
Alternative Generic Query if above fails:
SELECT 'SQL STATEMENT ' + COALESCE(N.NAME, CONVERT(VARCHAR, T.SQLHASH)) + ' NORMALLY AVERAGES ' + CONVERT(VARCHAR, CAST(ROUND(T.TOTAL_AVG, 3) AS FLOAT)) + ' BUT HAS RECENTLY AVERAGED ' + CONVERT(VARCHAR, CAST(ROUND(H.LAST_HOUR_AVG, 3) AS FLOAT)) + ' ---- SQL TEXT ---- ' + ST.ST
, CAST(ROUND(TOTAL_STDDEV, 1) AS FLOAT) AS TOTAL_STDDEV
FROM (
SELECT TOP 100 SQLHASH
, SUM(EXECS) TOTAL_EXECS
, SUM(TIMESECS) TOTAL_TIME
, ROUND(AVG(TIMESECS / EXECS), 3) TOTAL_AVG
, STDEV(TIMESECS / EXECS) TOTAL_STDDEV
FROM CON_STATS_SUM_#DBID#
WHERE EXECS > 0
AND DATEHOUR > DATEADD(DAY, - 14, CURRENT_TIMESTAMP)
GROUP BY SQLHASH
ORDER BY TOTAL_TIME DESC
) T
INNER JOIN (
SELECT SQLHASH
, SUM(EXECS) LAST_HOUR_EXECS
, AVG(TIMESECS / EXECS) LAST_HOUR_AVG
FROM CON_STATS_SUM_#DBID#
WHERE EXECS > 0
AND DATEHOUR >= DATEADD(HOUR, - 2, CURRENT_TIMESTAMP)
GROUP BY SQLHASH
) H
ON T.SQLHASH = H.SQLHASH
LEFT OUTER JOIN CON_SQL_NAME N
ON T.SQLHASH = N.HASH
INNER JOIN CONST_#DBID# ST
ON ST.H = T.SQLHASH
WHERE H.LAST_HOUR_AVG > T.TOTAL_AVG + (2 * T.TOTAL_STDDEV)