Lesson Learned #321:Useful Kusto Monitoring Queries for Azure SQL Database and Elastic Database Pool

[Jose_Manuel_Jurado]

You could find here useful Kusto queries to monitor Elastic Database Pool and Azure SQL Databases standalone. 

CPU Percentage

AzureMetrics
| where ResourceProvider == "MICROSOFT.SQL"
| where TimeGenerated >= ago(1d)
| where MetricName in ('cpu_percent')
| parse _ResourceId with * "/microsoft.sql/servers/" Resource
| summarize CPU_percent = max(Maximum) by Resource, MetricName

Data IO percentage.

AzureMetrics
| where ResourceProvider == "MICROSOFT.SQL"
| where TimeGenerated >= ago(1d)
| where MetricName in ('physical_data_read_percent')
| parse _ResourceId with * "/microsoft.sql/servers/" Resource
| summarize Physical_data_percent = max(Maximum) by Resource, MetricName

Log IO percentage.

AzureMetrics
| where ResourceProvider == "MICROSOFT.SQL"
| where TimeGenerated >= ago(1d)
| where MetricName in ('log_write_percent')
| parse _ResourceId with * "/microsoft.sql/servers/" Resource
| summarize LOG_Write_percent = max(Maximum) by Resource, MetricName

Summarized Query Duration

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents"
| where TimeGenerated >= ago(1d)
| where succeeded_s == 'true' 
| where statement_s != ''
| parse _ResourceId with * "/microsoft.sql/servers/" Resource
| summarize ExecutionCount = count(), AverageDuration_ms = avg(duration_milliseconds_d), MinDuration_ms = min(duration_milliseconds_d), MaxDuration_ms = max(duration_milliseconds_d) by Resource, Statement = statement_s
| order by MaxDuration_ms desc

 

Summarized Login Events

AzureDiagnostics
| where TimeGenerated >= ago(1d) 
| where action_name_s == "DATABASE AUTHENTICATION FAILED" or action_name_s == 'DATABASE AUTHENTICATION SUCCEEDED'
| extend additional_information_xml=parse_xml(additional_information_s)
| extend Error_Code=additional_information_xml.login_information.error_code
| extend Error_State=additional_information_xml.login_information.error_state
| extend Result = iif (succeeded_s == 'true', "Succeeded", "FAILED")
| parse _ResourceId with * "/microsoft.sql/servers/" Resource
| project TimeGenerated, Resource, Result, Error_Code, Error_State, Username = session_server_principal_name_s
| summarize Count = count() by Resource, Result, tostring(Error_Code), tostring(Error_State), Username
| order by Resource, Result, Error_Code, Error_State, Username

Detailed Login Events

AzureDiagnostics
| where TimeGenerated >= ago(1d) 
| where action_name_s == "DATABASE AUTHENTICATION FAILED" or action_name_s == 'DATABASE AUTHENTICATION SUCCEEDED'
| extend additional_information_xml=parse_xml(additional_information_s)
| extend Error_Code=additional_information_xml.login_information.error_code
| extend Error_State=additional_information_xml.login_information.error_state
| extend Result = iif (succeeded_s == 'true', "Succeeded", "FAILED")
| parse _ResourceId with * "/microsoft.sql/servers/" Resource
| project TimeGenerated, Resource, Result, Error_Code, Error_State, Username = session_server_principal_name_s, Application = application_name_s, ClientIP = client_ip_s, HostName = host_name_s, SessionID = session_id_d, TLSVersion = client_tls_version_name_s
| order by TimeGenerated desc

Execution Timeouts

Option 1 - Displays all execution timeouts (provides Query Text)

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents"
| where TimeGenerated >= ago(1d)
| where statement_s != '' and additional_information_s == '' and succeeded_s == 'false'
| extend Resource = strcat(server_instance_name_s, '/databases/' , database_name_s)
| project TimeGenerated, Resource, QueryText = statement_s, Duration_ms = duration_milliseconds_d, SessionID = session_id_d, ClientIP = client_ip_s, PrincipalName =  session_server_principal_name_s
| order by TimeGenerated desc  

Option 2 - Displays execution timeouts captured by Query Store (one hour intervals, provides query hash)

AzureDiagnostics
| where Category == "QueryStoreRuntimeStatistics"
| where execution_type_d == 3
| extend interval_start_time_date = tolong(interval_start_time_d) / 4294967296
| extend interval_start_time_time = interval_start_time_d - 4294967296 * interval_start_time_date
| extend interval_start = datetime(1900-1-1) + time(1d) * interval_start_time_date + time(1s) * (interval_start_time_time / 300.0)
| extend interval_end_time_date = tolong(interval_end_time_d) / 4294967296
| extend interval_end_time_time = interval_end_time_d - 4294967296 * interval_end_time_date
| extend interval_end = datetime(1900-1-1) + time(1d) * interval_end_time_date + time(1s) * (interval_end_time_time / 300.0)
| parse _ResourceId with * "/microsoft.sql/servers/" Resource
| project interval_start, interval_end, Resource, QueryHash = query_hash_s, QueryID =  query_id_d, ExecutionCount = count_executions_d, MaxDuration_ms = max_duration_d / 1000
| where interval_start >= ago(1d)
| order by interval_start desc 

Option 3 - Displays all timeouts but it will only provide query hash for the ones captured by Query Store

AzureDiagnostics
| where Category == "Timeouts"
| where TimeGenerated >= ago(1d)
| extend QueryHash = iff (query_hash_s == 0, 'UNKNOWN', strcat ('0x',tohex(tolong(query_hash_s))))
| parse _ResourceId with * "/microsoft.sql/servers/" Resource
| project TimeGenerated, QueryHash, Resource
| order by TimeGenerated desc  

Enjoy!