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)?
Yep, we have CurrentQueries!
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
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