Start a new topic
Implemented

Azure SQL Query Max Time Alert

Could you add an alert to Azure SQL Monitoring that would send an alert if there was a query/session that ran too long (past x-amount of minutes)?


Thanks,

Scott Weigand


Yep, we have CurrentQueries!

Scott,


Actually, this can be accomplished in an even simpler fashion.  We already have a metric that tracks currently executing queries.  It's category is: DatabaseSqlCurrentQueries

If you dont have such a metric defined, define it and then execute the alert on it as stated above.  ElapsedTime in it is the property for tracking elapsed time in milliseconds, but metric name may change in the "Any" expression

HTH


1 person likes this

Hi Scott,


This is possible to achieve by yourself without any modifications to CloudMonix.


This is a two-step process:


 - Define a new metric in your SQL Azure database, that tracks currently running queries.  The metric category for this is DatabaseSqlQuery.  The SQL Script for this can be something like as follows.  This metric will now track a list of currently executing queries and display it in a table as a separate view in the resource. Let's say this metric is named CurrentlyExecutingQueries
  

SELECT LEFT(sqltext.TEXT,255) as Text,
req.session_id as SessionID,
req.status as Status,
req.command as Command,
req.cpu_time as CPU,
req.total_elapsed_time as ElapsedTime
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

  


 - Now define an alert over this metric where "ElapsedTime" is over a certain period of time, and where alert expression is defined like so:

 

Any(CurrentlyExecutingQueries, "ElapsedTime > XXXX") 

  

and where XXX is some sort of threshold in milliseconds


Do ensure to "Test" the resource before saving to ensure that there are no issues with the syntax or your alert condition and permissions to run this query.  That's it


1 person likes this
Login or Signup to post a comment