Description
This alert will look for a plan change for the top 50 SQL statements (for the last 14 days). If there are plan changes detected, one email will be sent with all SQLs that experienced plan changes. This alert will work for Ignite 8.2 and higher for SQL Server instances.
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
Execution Interval: 10 minutes
Notification Text: The following SQL statements had a plan change recently.
SQL Statement:
SELECT sql_hash_or_name, COUNT(1) FROM (
SELECT DISTINCT COALESCE(n.name, convert(varchar,sw.izho)) sql_hash_or_name, orph plan_hash_value
FROM consw_#DBID# sw
LEFT OUTER JOIN con_sql_name n ON n.HASH = sw.izho
INNER JOIN (
SELECT TOP 50 sqlhash, SUM(timesecs) timesecs
FROM con_sql_sum_#DBID# ss
WHERE datehour > CURRENT_TIMESTAMP - 14.0
GROUP BY sqlhash
ORDER BY SUM(timesecs) DESC) topn on topn.sqlhash = sw.izho
WHERE sw.d >= CURRENT_TIMESTAMP - (1.0*#FREQUENCY#/1440)
AND sw.izho <> 0 AND sw.orph <> 0) sql_and_plan
GROUP BY sql_hash_or_name
HAVING COUNT(1) > 1
Execute Against: Repository
Units: Number of Plan Changes
High Threshold: Min 1, Max empty
