4 Replies Latest reply on Apr 21, 2015 9:54 AM by edcarden

    SolarWinds Database Performance Analyzer SLA monitoring and tracking?

    jahaines82

      Hi,

       

      I am looking for a performance monitoring solution that I can also leverage for SLA tracking/monitoring. I love what SolarWinds DPA can do from a performance perspective, but can SolarWinds DPA also give me the ability to track and report SLA metrics?  What I am looking for specifically is a tool that allows me to dashboard aggregate metrics such as n% of queries/stored procedures are within SLA guidelines.  A nice to have would be an alert when a query/stored procedure exceeds the SLA threshold.

       

      Thanks,

      Adam Haines

        • Re: SolarWinds Database Performance Analyzer SLA monitoring and tracking?

          This is a great feature request.  I've submitted it here >> https://thwack.solarwinds.com/ideas/5052

          • Re: SolarWinds Database Performance Analyzer SLA monitoring and tracking?
            edcarden

            jahaines82

             

            The following is something I came up with to alert us whenever a query's execution time has exceeded a specified threshold. Obviously this does not cover a full set of SLA's but it might prove useful as a starting point.  I set this up as an alert that ran every minute and so anytime a query's execution was taking longer then 1 minute I was immediately alerted.  I used DPA's alert functionality to push to me the info needed when any query was taking long enough to warrant a review.

             

            DECLARE @MetricThreshold INT
            SET @MetricThreshold = 1000
            SELECT 'LoginID=' + S.original_login_name
                + ';SPID' + Convert(VARCHAR, A.session_id)
                + ';HOST=' + S.host_name
                + ';App=' + S.program_name
                + ';CMD=' +  A.command
                + ';Duration=' + Convert(VARCHAR,DATEDIFF(ms, a.start_time, CURRENT_TIMESTAMP)) + 'millisec(s);'
                + 'TSQL=' + Convert(VARCHAR(256),b.text), Convert(VARCHAR,Datediff(ms, a.start_time, CURRENT_TIMESTAMP))
            FROM sys.dm_exec_requests a Join sys.dm_exec_sessions S ON A.session_id = S.session_id
                                CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
            WHERE 1 = 1
              AND S.program_name NOT LIKE 'SQL Monitor%' /*Filters out anything that SQL Monitor is doing*/
              AND A.command      NOT LIKE'DBCC%TABLE%CHECK%'  /*Filters out SQL Job that runs DBCC CHECKBD */
              AND A.command      NOT LIKE 'BACKUP%DATABASE%'  /*Filters out DB Backup calls*/
              AND A.Status <> 'background'
              AND Datediff(ms, a.start_time, CURRENT_TIMESTAMP) > @MetricThreshold