Azure SQL: find long queries in the audit logs with KQL
Two KQL queries on the SQLSecurityAuditEvents category: number of long statements per database, then long statements grouped by text.
Azure SQL Database can send its audit events to a Log Analytics workspace. The SQLSecurityAuditEvents category lands in the AzureDiagnostics table, with the statement text and its duration in milliseconds. The two queries below use that data to find slow statements. They keep statements longer than 1 second (1000 ms) and skip statements containing hangfire, an example filter for a background-job library; remove that filter if it does not apply.
Number of long statements per database
//------------------------- scope sql server -------------
//number of long queries per database
AzureDiagnostics
| where ResourceProvider == 'MICROSOFT.SQL'
| where statement_s !has 'hangfire' and duration_milliseconds_d > 1000
| where Category == 'SQLSecurityAuditEvents'
| summarize CountValue = count(), AvgDuration = avg(duration_milliseconds_d), TotalDuration = sum(duration_milliseconds_d) by bin(TimeGenerated, 60m), server_instance_name_s, database_name_s
| extend AvgDurationDescr = case (
AvgDuration < 1000, strcat( round(AvgDuration,1) ,'ms'),
AvgDuration >= 1000 and AvgDuration < 60000, strcat(round(AvgDuration / 1000 ,1) , 'sec'),
AvgDuration >= 60000, strcat(round(AvgDuration / 1000 / 60 ,1) , 'min'),
strcat(AvgDuration, ''))
| extend TotalDurationDescr = case (
TotalDuration < 1000, strcat( round(TotalDuration,1) ,'ms'),
TotalDuration >= 1000 and TotalDuration < 60000, strcat(round(TotalDuration / 1000 ,1) , 'sec'),
TotalDuration >= 60000 and TotalDuration < 3600000, strcat(round(TotalDuration / 1000 / 60 ,1) , 'min'),
TotalDuration >= 3600000 and TotalDuration < 86400000, strcat(round(TotalDuration / 1000 / 60 / 60 ,1) , 'hr'),
TotalDuration >= 86400000, strcat(round(TotalDuration / 1000 / 60 / 60 / 24 ,1) , 'day'),
strcat(TotalDuration, ''))
Long statements grouped by text
The query cuts each statement before its parameter list (N'@...), so that the same statement with different values is counted once.
//List of long queries, including avg duration and distinct count of databases
AzureDiagnostics
| where ResourceProvider == 'MICROSOFT.SQL'
| where statement_s !has 'hangfire' and duration_milliseconds_d > 1000
| where Category == 'SQLSecurityAuditEvents'
| extend placeOfSeparation = indexof(statement_s,"N'@"), lengthStatement = strlen(statement_s)
| extend partOfQuery = case(placeOfSeparation==-1,statement_s , substring(statement_s,0,lengthStatement - (lengthStatement - placeOfSeparation)))
| summarize CountValue = count(), DiffDb = dcount(database_name_s), AvgDuration = round(avg(duration_milliseconds_d),0), TotalDuration = sum(duration_milliseconds_d) by bin(TimeGenerated, 7d), partOfQuery
| extend AvgDurationDescr = case (
AvgDuration < 1000, strcat( round(AvgDuration,1) ,'ms'),
AvgDuration >= 1000 and AvgDuration < 60000, strcat(round(AvgDuration / 1000 ,1) , 'sec'),
AvgDuration >= 60000, strcat(round(AvgDuration / 1000 / 60 ,1) , 'min'),
strcat(AvgDuration, ''))
| extend TotalDurationDescr = case (
TotalDuration < 1000, strcat( round(TotalDuration,1) ,'ms'),
TotalDuration >= 1000 and TotalDuration < 60000, strcat(round(TotalDuration / 1000 ,1) , 'sec'),
TotalDuration >= 60000 and TotalDuration < 3600000, strcat(round(TotalDuration / 1000 / 60 ,1) , 'min'),
TotalDuration >= 3600000 and TotalDuration < 86400000, strcat(round(TotalDuration / 1000 / 60 / 60 ,1) , 'hr'),
TotalDuration >= 86400000, strcat(round(TotalDuration / 1000 / 60 / 60 / 24 ,1) , 'day'),
strcat(TotalDuration, ''))
| order by CountValue desc;
Notes
- The audit category must be enabled in the diagnostic settings of the database or server for these rows to exist.