如何编写Kusto Query排查Azure SQL数据库高CPU占用任务？

AppEvents确实无法获取Azure SQL的CPU使用率及任务详情，你需要使用Azure Monitor中专门针对SQL数据库的监控表来排查。以下是几种实用的Kusto查询方案：

1. 先确认高CPU的具体时段 #

先通过数据库级别的CPU使用率趋势，锁定CPU接近100%的精确时间窗口：

AzureDiagnostics
| where ResourceType == "SQLDATABASES"
| where OperationName == "CPU usage"
| where _ResourceId contains "/subscriptions/你的订阅ID/resourceGroups/你的资源组/providers/Microsoft.Sql/servers/你的SQL服务器/databases/目标数据库名"
| where TimeGenerated between (datetime(2024-05-01 08:00:00) .. datetime(2024-05-01 10:00:00)) // 替换为你的可疑时段
| summarize avg(CpuPercent) by bin(TimeGenerated, 1m), _ResourceId
| render timechart

2. 定位高CPU消耗的具体请求 #

使用SqlRequests表（记录SQL请求的详细性能数据），筛选目标时段内CPU消耗最高的任务：

SqlRequests
| where DatabaseName == "目标数据库名"
| where TimeGenerated between (datetime(2024-05-01 08:30:00) .. datetime(2024-05-01 08:45:00)) // 替换为第一步锁定的高CPU时段
| where CpuTimeMs > 0
| summarize 
    TotalCpuMs = sum(CpuTimeMs),
    AvgCpuMs = avg(CpuTimeMs),
    RequestCount = count(),
    MaxDurationMs = max(DurationMs)
    by 
    QueryText,
    SessionId,
    ClientIp,
    bin(TimeGenerated, 30s)
| order by TotalCpuMs desc
| take 20 // 取Top20高CPU请求

这个查询会按总CPU消耗排序，帮你快速找到最耗CPU的查询文本、发起客户端IP及会话信息。

3. 基于Query Store的补充查询 #

如果SqlRequests数据不全，可借助Query Store的统计数据（需确保数据库已启用Query Store，默认启用）：

AzureDiagnostics
| where ResourceType == "SQLDATABASES"
| where OperationName == "QueryStore Runtime Statistics"
| where DatabaseName == "目标数据库名"
| where TimeGenerated between (datetime(2024-05-01 08:30:00) .. datetime(2024-05-01 08:45:00))
| project 
    TimeGenerated,
    QueryText = query_text_s,
    TotalCpuMs = cpu_time_ms_d,
    ExecutionCount = execution_count_d,
    AvgCpuPerExecution = cpu_time_ms_d / execution_count_d
| order by TotalCpuMs desc
| take 20

4. 关联会话信息排查来源 #

若需要进一步定位请求发起者，可关联SqlSessions表获取登录名、应用程序名：

SqlRequests
| where DatabaseName == "目标数据库名"
| where TimeGenerated between (datetime(2024-05-01 08:30:00) .. datetime(2024-05-01 08:45:00))
| where CpuTimeMs > 0
| join kind=inner (
    SqlSessions
    | where DatabaseName == "目标数据库名"
    | project SessionId, LoginName, ApplicationName, ClientIp
) on SessionId
| summarize 
    TotalCpuMs = sum(CpuTimeMs),
    RequestCount = count()
    by 
    QueryText,
    LoginName,
    ApplicationName,
    ClientIp,
    bin(TimeGenerated, 30s)
| order by TotalCpuMs desc
| take 20

内容的提问来源于stack exchange，提问作者Jose Guerrero