如何在Log Analytics的KQL查询中关联对应query_id的SQL语句文本？

现有查询及结果 #

你当前使用的KQL查询可从Log Analytics中按总CPU时间返回Top3的query_id：

AzureDiagnostics
| where TimeGenerated >= ago(1h)
  and Category == 'QueryStoreRuntimeStatistics'
| summarize total_cpu_time = sum(cpu_time_d) by query_id_d
| top 3 by total_cpu_time desc

查询结果：

需求 #

如何从源Azure SQL数据库导入每个query_id对应的SQL语句文本，使结果新增query_text列，实现类似直接使用Azure SQL Query Store的效果？期望结果如下：

解决方案 #

方案1：使用KQL的externaldata函数直接关联 #

通过externaldata函数连接到目标Azure SQL数据库，读取Query Store的sys.query_store_query视图获取query文本，再与Log Analytics的统计结果关联。

示例查询：

// 第一步：获取Log Analytics中的Top3 CPU消耗统计
let top_cpu_queries = AzureDiagnostics
| where TimeGenerated >= ago(1h)
  and Category == 'QueryStoreRuntimeStatistics'
| summarize total_cpu_time = sum(cpu_time_d) by query_id_d;

// 第二步：从Azure SQL数据库导入query_id对应的SQL文本
let query_text_lookup = externaldata(query_id: long, query_text: string)
[
  h@'Server=tcp:<你的SQL服务器名>.database.windows.net,1433;Database=<数据库名>;Authentication=Active Directory Integrated;'
  with (format='sql', query='SELECT query_id, query_text FROM sys.query_store_query')
];

// 第三步：关联数据集并输出结果
top_cpu_queries
| join kind=inner (query_text_lookup) on $left.query_id_d == $right.query_id
| project query_id_d, total_cpu_time, query_text
| top 3 by total_cpu_time desc

注意事项：

• 替换<你的SQL服务器名>和<数据库名>为实际Azure SQL资源信息
• 执行查询的账号需同时拥有Log Analytics读取权限，以及Azure SQL数据库中访问sys.query_store_query视图的SELECT权限

方案2：通过Azure Monitor工作簿多数据源关联 #

如果不想在KQL中编写数据库连接，可在Azure Monitor工作簿中操作：

1. 添加第一个数据源：选择Log Analytics，运行你现有的KQL查询获取CPU统计结果
2. 添加第二个数据源：选择Azure SQL数据库，运行SELECT query_id, query_text FROM sys.query_store_query获取查询文本
3. 在工作簿中配置两个数据集的关联规则，基于query_id字段合并，最终展示包含query_text的完整结果

内容的提问来源于stack exchange，提问作者Eric Russell