Description
This alert will determine if any SQL had a plan change that resulted in a higher amount of wait time. Plan changes are not necessarily bad, but if the wait times go up, that is a good indication the new plan is not as good as the previous.
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: Plan Changes with Higher Wait Times
Execution Interval: 1 Hour
Notification Text: The message to deliver with the alert
SQL Script:
DECLARE @Recent decimal(7,5), @History smallint, @MinPctIncrease smallint, @MinTime int;
-- Recent is how far back to look at current data. Use 1.5 to make sure we get the last hourly numbers
SET @Recent = 1.5/24.0;
-- History is how far to go back to determine what normal is
SET @History = 7;
-- MinPctIncrease limits the SQLs that had plan changes.
-- The current times have to increase more than 50% before getting flagged
SET @MinPctIncrease = 20;
-- MinTime also limits the SQLs that had plan changes
-- The current (Recent) wait times have to be more than 100 seconds
SET @MinTime = 100;
WITH plan_changes (sqlhash, planhash)
AS (
SELECT sqlhash, COUNT(1) as count FROM (
SELECT DISTINCT sqlhash, planhash
FROM CON_PLAN_SUM_#DBID# ps
WHERE DATEHOUR >= CURRENT_TIMESTAMP - @Recent) a
GROUP BY sqlhash
HAVING COUNT(1) > 1
)
SELECT 'SQLHash: '+COALESCE(n.name, CONVERT(varchar,recent.sqlhash)) +
', SQLText: ' +SUBSTRING(SUBSTRING(st.st, st.ss, ABS(st.se-st.ss)), 1, 50) +
', Execs: '+CONVERT(varchar,recent.execs)+
', Seconds: '+CONVERT(varchar,recent.timesecs)+
', Recent Avg: '+CONVERT(varchar,recent.avg_exec_secs)+
', History Avg: '+ CONVERT(varchar,history.avg_exec_secs),
100*(recent.avg_exec_secs - history.avg_exec_secs) / history.avg_exec_secs pct_increase
FROM (
SELECT ss_cur.sqlhash, SUM(ss_cur.execs) execs, SUM(ss_cur.timesecs) timesecs, SUM(ss_cur.timesecs) / SUM(ss_cur.execs) avg_exec_secs
FROM CON_STATS_SUM_#DBID# ss_cur
INNER JOIN plan_changes pc1 ON pc1.sqlhash = ss_cur.sqlhash
WHERE ss_cur.DATEHOUR >= CURRENT_TIMESTAMP - @Recent
AND ss_cur.execs>0
GROUP BY ss_cur.sqlhash) recent
INNER JOIN (
SELECT ss_hist.sqlhash, SUM(ss_hist.execs) execs, SUM(ss_hist.timesecs) timesecs, SUM(ss_hist.timesecs) / SUM(ss_hist.execs) avg_exec_secs
FROM CON_STATS_SUM_#DBID# ss_hist
INNER JOIN plan_changes pc2 ON pc2.sqlhash = ss_hist.sqlhash
WHERE ss_hist.DATEHOUR >= CURRENT_TIMESTAMP - @History
AND ss_hist.execs>0
GROUP BY ss_hist.sqlhash) history ON history.sqlhash = recent.sqlhash
LEFT OUTER JOIN con_sql_name n ON n.hash = recent.sqlhash
INNER JOIN const_#DBID# st ON st.h = recent.sqlhash AND st.p = 0
WHERE (recent.avg_exec_secs - history.avg_exec_secs) / history.avg_exec_secs >= (@MinPctIncrease/100.0)
AND recent.timesecs >= @MinTime
ORDER BY recent.timesecs DESC;
Execute Against: Repository
Units: % Increase
High Threshold: Min 50, Max empty
Medium Threshold: Min 30, Max 50