Custom Alert - Long Running Queries

FormerMember
FormerMember

DESCRIPTION

This alert is for SQL Server instances only and will tell you when SQL statements have executed more than a defined amount of time. This example uses a 300 second threshold, meaning any SQL statements that have been executing for longer than 300 seconds (5 minutes) will be alerted. The alert query executes against the monitored database directly so caution should be taken on how often the alert is executed, especially if less than the default of every 300 seconds.

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: SQL Server - Long Running Queries

Execution Interval: 5 Minutes

Notification Text: The following list of SQL statements have been running for over 5 minutes.

SQL Statement:

SELECT

'SPID='+CONVERT(VARCHAR,a.session_id)+' has been running the following for '+CONVERT(VARCHAR,DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP))+' seconds: '+convert(varchar,b.text), CONVERT(VARCHAR,DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP))

FROM sys.dm_exec_requests a

CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b

WHERE a.status <> 'background'

AND DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP) > 300 --This value of 300 must match the Execution Interval set in the alert

Execute Against: Monitored Database

Units: Seconds

High Threshold: MIN 300 (This value must match value found in query. Default: 300 ), MAX empty

  • Very helpful custom alert, thank you for providing that for community!

    I just tried to extend this report e-mail output with long running query and hash added into the output:

    SELECT 'session_id ' +

    CONVERT(VARCHAR,a.session_id) + ' query_hash: '+ convert(varchar, a.query_hash, 2) +' seconds: ' + CONVERT(VARCHAR,DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP)) +' sql_text: '+ convert(varchar(8000),b.text) +' query_hash: '+ convert(varchar, a.query_hash, 2) +' query_plan_hash: ' + convert(varchar,a.query_plan_hash, 2), CONVERT(VARCHAR,DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP))

    FROM sys.dm_exec_requests a

    CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b

    WHERE a.status <> 'background'

    AND DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP) > 1

    I tested that query on my database and got full queries text output,  what's happened with my ignite alert output it's just a short begin part of the query, the question - how is it possible to get full long running sql queries output in addition to the current e-mail report?

  • If you are still running as an alert, unfortunately, we are limited to 100 characters currently for the output.  That's part of our data model and probably not easily addressed.  Have you submitted this as a feature request here on Thwack?

  • Not yet, it would be great to get this feature in feature, how to submit it?

    Thanks!

  • If you click on the link below, you can submit and it can get voted up.  Our product managers monitor these requests.

    Database Performance Analyzer Feature Requests

  • Hello!

    Could you answer to couple of questions,

    1. I'm watching statistics of the  highest execution time available queries in Ignite -> Mydatabaseserver -> Current -> Long Running (Average) list. This is a highest average execution time window fixed by Ignite for the last 1 hour. What is the difference between "CURRENT_TIMESTAMP" value configured for checking in your alert and Long Running (Average) one hour monitoring current window check in Ignite, can you reconfigure your alert to the same "Long Running Average" check?

    2. How can we build "Long Running Average" queries history log for all time, instead of just one hour real time Current window, do we need to create custom report, or maybe some Ignite features/custom reports already available?



    Thanks!

  • 1. The alerts and the calc for the long running list are not kept in sync.  I believe the long running average list is updated every 10 minutes (tied to our stats poll where we collect the number of executions).  This would make sense as we can't do an average execution time until we know how many times it got executed.  If you go to your logs directory, you should see a contime.csv file which you can open in excel.  You can use sorting to look for the activity "SQLSTATS-FETCH" and look at the latest runs to see when they are executing (for example 5:52, 6:02, 6:12, etc.). Then you can try to create your alert or activate it at the same time-ish and use a 10 minute interval.  Now when DPA gets cycled or when monitoring gets interrupted for whatever reason, the statspoll may shift right or left, but the alert likely will not, so there will be drift.  Short answer is that this would be very difficult to keep in sync with our current methodology (IMO).

    2. If you have identified the long running average queries you want to focus more on, I'd recommend using the View Historical Chart feature in the tool which will provide average execution times per day for a historical setting.  Take a look at that and see if that meets what you need.

  • Thanks for so great explanation! I'm going to check it now,

  • Could you help with converting our alerts sql hash ' query_hash: ' which looks like that from e-mail -"65C0914DFC1B49C4" to ignite web sql hash which looks like that - ""4389872600" for next search in ignite? From our new custom modified alert:

    SELECT 'session_id ' +

    CONVERT(VARCHAR,a.session_id) + ' query_hash: '+ convert(varchar, a.query_hash, 2) +' query_plan_hash: ' + convert(varchar,a.query_plan_hash, 2) + ' seconds: ' + CONVERT(VARCHAR,DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP)) +' sql_text: '+ convert(varchar(8000),b.text) , CONVERT(VARCHAR,DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP))

    FROM sys.dm_exec_requests a

    CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b

    WHERE a.status <> 'background'

    AND DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP) > 3 -- for children of nature, more than 60 seconds execution time

    Thanks!

  • That we can't do.  The query you are running for the alert as defined runs directly against the monitored instance.  The query_plan_hash being selected is directly from the sys.dm_exec_requests DMV and DPA isn't doing it's own hash as it never actually flows through our app (we only show what SQL shows us).

    Sorry about that!