This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Custom Alert - Plan Changes with Higher Wait Times

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

  • This is great!!! Can we have an Oracle version of this?

  • Hmm... That is a bit different from what we want, we want to be informed on plan changed that resulted in higher wait time, not the number of plan changes.

  • Here is the Oracle syntax excluding the variables.

    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 - 20) a

    GROUP BY sqlhash

    HAVING COUNT(1) > 1

       )

    SELECT 'SQLHash: '|| COALESCE(TO_CHAR(n.name), TO_CHAR(recent.sqlhash)) ||

    ', SQLText: ' || SUBSTR(SUBSTR(st.st, st.ss, ABS(st.se-st.ss)), 1, 50) ||

    ', Execs: '|| TO_CHAR(recent.execs) ||

    ', Seconds: '|| TO_CHAR(recent.timesecs) ||

           ', Recent Avg: ' || TO_CHAR(recent.avg_exec_secs) ||

    ', History Avg: ' || TO_CHAR(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 - (1.5/24.0)

       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 - 7

       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 >= (20/100.0)

    AND recent.timesecs >= 100

      ORDER BY recent.timesecs DESC

  • Adding MySQL too:

    SELECT CONCAT('SQLHash: ', COALESCE(n.name, CONVERT(recent.sqlhash, CHAR))

    , 'SQLText: ', SUBSTRING(SUBSTRING(st.st, st.ss, ABS(st.se-st.ss)), 1, 50)

    , 'Execs: ', CONVERT(recent.execs, CHAR)

    , 'Seconds: ', CONVERT(recent.timesecs, CHAR)

    , 'Recent Avg: ', CONVERT(recent.avg_exec_secs, CHAR)

    , 'History Avg: ', CONVERT(history.avg_exec_secs, CHAR))

    , 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

       (

    SELECT sqlhash, COUNT(1) as count FROM (

    SELECT DISTINCT sqlhash, planhash

    FROM CON_PLAN_SUM_#DBID# ps

    WHERE DATEHOUR >= CURRENT_TIMESTAMP - 1.5/24.0) a

    GROUP BY sqlhash

    HAVING COUNT(1) > 1

       ) pc1 ON pc1.sqlhash = ss_cur.sqlhash

       WHERE ss_cur.DATEHOUR >= CURRENT_TIMESTAMP - 1.5/24.0

       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 (

    SELECT sqlhash, COUNT(1) as count FROM (

    SELECT DISTINCT sqlhash, planhash

    FROM CON_PLAN_SUM_#DBID# ps

    WHERE DATEHOUR >= CURRENT_TIMESTAMP - 1.5/24.0) a

    GROUP BY sqlhash

    HAVING COUNT(1) > 1

       ) pc2 ON pc2.sqlhash = ss_hist.sqlhash

       WHERE ss_hist.DATEHOUR >= CURRENT_TIMESTAMP - 7

       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 >= (20/100.0)

    AND recent.timesecs >= 100

      ORDER BY recent.timesecs DESC