DPA Alerts for *downward* trends?

A couple of ideas, but please note these are not tested.

1. Set up an alert on wait time for a single SQL (maybe Average Wait Time for a Single SQL) - but set the High threshold as a low range, rather than a high range. But, being an average, this may not be a timely alert as it will potentially take time for the average to drop sufficiently to trip the alert.
2. Plan B - Set up a custom alert to monitor the last time the SQL was executed (or perhaps the number of times executed etc) - example code borrowed from here:sql server - how to get History of queries executed with username in SQL - Database Administrators Stack Exchange

SELECT deqs.last_execution_time AS [Time], deqs.execution_count AS [Count]

    ,dest.TEXT AS [Query]

FROM sys.dm_exec_query_stats AS deqs

CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

ORDER BY deqs.last_execution_time DESC

Modify the above SQL for your specific query, and get it to return a value to trigger the alert (number of seconds elapsed since last run or something)

So if last_execution_time is e.g.  > 5 minutes ago, raise an alert

Or if execution_count is static for 5 minutes raise an alert (but this may take more coding to track)

We have an alert that looks for PLE, which alerts on low values.

It is a Custom SQL Alert - Single Alert Status Return

This type of alert expects a single string value to be returned including one of the following values: NORMAL, INFO, LOW, MEDIUM, HIGH.  We use this to alert us when PLE drops below a threshold.

Example SQL:

SELECT

       CASE  WHEN [cntr_value] < 500 THEN 'HIGH'

                  WHEN [cntr_value] < 700 THEN 'MEDIUM'

   WHEN [cntr_value] < 900 THEN 'LOW'

  ELSE 'NORMAL' END AS AlertString

FROM sys.dm_os_performance_counters

WHERE [object_name] LIKE '%Manager%'

AND [counter_name] = 'Page life expectancy'

All you would need to do is have a query that checks instant wait time, or executions per timeframe, and if it drops down, throw a HIGH alert.

Thanks to you both, gcp and houndin, for the replies.

I marked houndin's response as 'correct' as it most closely matches what we ended up going with.  Ultimately we were not forced to consider strict measures like query runtime over time, as our app vendor was able to supply us with detailed queries to determine query status.  We ended up with an IF-THEN-ELSE kind of logic and, used the custom sql alert with single alert status return to trigger alerts according to the severity of each condition.  Very much like what houndin put forward.

Thanks again!

Thank you for closing the loop!