LLI Wiki — My Digital Knowledge Base

KQL: finding long transactions in Azure SQL Database

KQL queries on AzureDiagnostics to list a transaction's statements and detect long transactions.

With Azure SQL Database diagnostics (auditing) sent to Log Analytics, the AzureDiagnostics table lets you find a database's transactions and spot the long-running ones. The queries below are written in KQL.

All queries of one transaction

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL" and database_name_s == 'sqldb-example'
| where transaction_id_d == 123456789
| project TimeGenerated, statement_s, duration_milliseconds_d, action_name_s, succeeded_s, transaction_id_d

All transactions of one session

For a given database and session_id:

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL" and database_name_s == 'sqldb-example'
| where action_name_s has 'transaction' and session_id_d == 192
| project TimeGenerated, statement_s, duration_milliseconds_d, action_name_s, succeeded_s, transaction_id_d, session_id_d

Long transactions

The query pairs each TRANSACTION BEGIN COMPLETED event with the next event of the same session and database (commit or rollback), then computes the duration in seconds. Here the threshold is TransactionDur > 5: adjust the value (for example 30 for transactions longer than 30 seconds).

let transactionInfo =
AzureDiagnostics
//| where TimeGenerated >= datetime("2024-01-01 00:00") and TimeGenerated <= datetime("2024-01-01 10:00")
| where ResourceProvider == "MICROSOFT.SQL" and database_name_s has 'sqldb-example' //and session_id_d == 52
| where action_name_s has 'transaction'
| project TimeGenerated, event_time_t, action_name_s, transaction_id_d, session_id_d, database_name_s
| extend ActionOrder = iif(action_name_s == 'TRANSACTION BEGIN COMPLETED',1,2)
| order by database_name_s, session_id_d asc, event_time_t asc, ActionOrder asc
| extend TransactionEnd = iif(action_name_s == 'TRANSACTION BEGIN COMPLETED' and session_id_d == next(session_id_d) and database_name_s == next(database_name_s), next(TimeGenerated), datetime(null))
| extend TransactionDur = datetime_diff('second',TransactionEnd,TimeGenerated)
| extend TransactionSucceeded = iif(next(action_name_s) == 'TRANSACTION ROLLBACK COMPLETED','rollback','commit');
transactionInfo
| where action_name_s == 'TRANSACTION BEGIN COMPLETED' and TransactionDur > 5
| project TimeGenerated, TransactionEnd, transaction_id_d, session_id_d, TransactionDur, TransactionSucceeded
//| order by database_name_s asc, TimeGenerated asc

Notes

Mastodon LLIMastodon SocialFosstodon