4 Replies Latest reply on Mar 3, 2017 2:05 PM by houndin

    DPA Alerts for *downward* trends?

    phughes

      Hi all.


      We're still relatively new to DPA, and in reviewing discussion threads I'm unable to find out whether it's possible to produce alerts for downward activity trends in DPA, or alerts when query waits drop significantly.


      We are monitoring a SQL server instance in which an important query effectively runs all times, by design.  We're seeing issues in which the service responsible for this query goes into a non-responsive state, and the query stops running.  Client user impact is not immediately obvious, and the service itself does not crash or produce an event for which we can monitor using SCOM, etc. 


      We can see evidence of this by looking at an hourly graph of query waits on the affected SQL Server instance, in which the bar for the query in question shrinks significantly or disappears at some point.  Overall waits on the server notably decrease, and performance actually improves, but in this case, that is not a good thing.  For obvious reasons, watching a graph is not practical for monitoring purposes.

       

      Essentially, we are looking for a way to monitor for a negative result - the absence of a query - or for a sharp downward trend in waits, either on that specific query or on the server overall.  Any guidance would be much appreciated.

        • Re: DPA Alerts for *downward* trends?
          gcp

          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)

          • Re: DPA Alerts for *downward* trends?
            houndin

            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.

            • Re: DPA Alerts for *downward* trends?
              phughes

              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!