如何从Azure SQL Analytics日志错误信息定位到对应的SQL查询

为什么错误日志中query hash显示为-1 #

出现该值通常对应以下场景：

• 执行的是未被查询存储/计划缓存捕获的即席单次查询
• 错误由动态SQL、存储过程内部临时语句触发
• 语句长度过短或为系统内部执行的非用户查询
• 查询存储捕获模式默认为AUTO，未捕获低资源消耗的查询

针对Azure SQL Analytics日志的Kusto查询 #

你可以用以下语句关联错误日志与查询性能日志，拉取同一时间窗口内的关联查询上下文：

// 替换为你的错误发生时间范围、错误号
let errorStartTime = datetime(2024-01-01 12:00:00);
let errorEndTime = datetime(2024-01-01 12:05:00);
let targetErrorNumber = 1205; // 示例为死锁错误号，替换为你的实际错误号
AzureDiagnostics
| where TimeGenerated between (errorStartTime .. errorEndTime)
| where Category == "SQLErrors" and error_number_d == targetErrorNumber
| extend ErrorTime = TimeGenerated
| join kind=inner (
    AzureDiagnostics
    | where TimeGenerated between (errorStartTime .. errorEndTime)
    | where Category == "QueryStoreRuntimeStatistics"
    | project QueryTime = TimeGenerated, query_hash_s, query_text_s, database_name_s
) on database_name_s
// 匹配错误发生前后10秒内的查询，可根据需要调整时间窗口
| where abs(datetime_diff('second', ErrorTime, QueryTime)) < 10
| project ErrorTime, error_number_d, error_message_s, query_hash_s, query_text_s, database_name_s

可在托管实例直接执行的T-SQL查询 #

1. 从查询存储拉取指定时间范围的SQL查询 #

如果你的托管实例已开启查询存储（默认开启），可直接执行以下语句获取指定时间窗口内的查询及关联对象信息：

-- 替换为实际的时间范围、错误关联的数据库
USE [你的数据库名]
GO
SELECT 
    qt.query_sql_text,
    q.query_hash,
    qt.statement_sql_handle,
    rs.count_executions,
    rs.avg_duration,
    OBJECT_NAME(q.object_id) AS related_object, -- 查询所属存储过程/函数名，即席查询则为NULL
    rs.runtime_stats_start_time,
    rs.runtime_stats_end_time
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_runtime_stats rs ON q.query_id = rs.query_id
JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
-- 调整为你要查询的时间范围
WHERE rsi.start_time >= '2024-01-01 12:00:00' 
AND rsi.end_time <= '2024-01-01 12:10:00'
-- 可添加过滤条件缩小范围，比如按执行时长、错误关联的表名关键词等
-- AND qt.query_sql_text LIKE '%你要找的表名%'
ORDER BY rsi.start_time DESC

2. 临时捕获实时错误关联SQL（如果问题可复现） #

如果问题可以复现，建议创建临时扩展事件会话捕获错误对应的完整SQL上下文：

CREATE EVENT SESSION [Capture_Error_Queries] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.sql_text,sqlserver.query_hash,sqlserver.username,sqlserver.client_app_name)
    WHERE severity >= 16 -- 只捕获错误级别>=16的事件，可调整
),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.sql_text,sqlserver.query_hash)
)
ADD TARGET package0.event_file(SET filename=N'Capture_Error_Queries.xel',max_file_size=(50))
WITH (STARTUP_STATE=OFF)
GO
-- 启动会话
ALTER EVENT SESSION [Capture_Error_Queries] ON SERVER STATE = START;
-- 问题复现后停止会话
-- ALTER EVENT SESSION [Capture_Error_Queries] ON SERVER STATE = STOP;

补充优化建议 #

如果频繁出现query_hash为-1的情况，建议调整查询存储捕获模式为ALL，确保所有用户查询都被捕获：

ALTER DATABASE [你的数据库名] 
SET QUERY_STORE (OPERATION_MODE = READ_WRITE, CAPTURE_MODE = ALL);

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