SQL Server特定EF生成查询执行超时无法完成，求排查方案

1. 定位"隐形"执行计划的真实状态 #

你提到能从sys.dm_exec_sql_text拿到plan handle，但计划不在sys.dm_exec_cached_plans中，可能是计划已被移出缓存但请求记录未及时清理，或是查询执行时的临时计划。可以通过以下查询捕获执行时的完整上下文：

SELECT 
    r.session_id,
    r.status,
    r.command,
    t.text AS query_text,
    p.query_plan,
    r.plan_handle,
    r.sql_handle,
    r.parameters
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) p
WHERE r.session_id <> @@SPID;

该查询能返回当前执行请求的参数、计划详情，哪怕计划未被缓存。

2. 绕过EF限制强制查询重新编译 #

由于无法修改EF生成的查询，可通过数据库端的计划指南强制该查询每次执行都重新编译：

1. 从Profiler或sys.dm_exec_sql_text中复制EF生成的原始查询文本
2. 创建计划指南：

EXEC sp_create_plan_guide 
    @name = N'Guide_EF_Problem_Query',
    @stmt = N'-- 替换为EF生成的原始查询文本',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = N'OPTION (RECOMPILE)';

此操作会让SQL Server每次执行该查询时生成新计划，避开潜在的坏计划。

3. 对比查询文本差异导致的计划区别 #

移除方括号后查询变快，说明两段查询的哈希值不同，SQL Server视为不同查询生成了不同计划。可通过以下步骤定位差异根源：

• 对比两个查询的执行计划：

SET SHOWPLAN_XML ON;
GO
-- 执行EF生成的原始查询
GO
SET SHOWPLAN_XML OFF;

SET SHOWPLAN_XML ON;
GO
-- 执行修改后的查询（移除方括号）
GO
SET SHOWPLAN_XML OFF;

查看两个计划的索引选择、连接方式、行数估计差异，明确慢计划的瓶颈点。

• 检查自动参数化设置差异：

SELECT name, is_auto_param_on, is_auto_param_forced_on 
FROM sys.databases WHERE name = N'你的数据库名';

若is_auto_param_forced_on为ON，可能导致EF查询的自动参数化计划不匹配实际数据分布。

4. 排查资源层面的隐性瓶颈 #

超时未必是执行计划问题，可能是资源不足导致：

• 查看等待类型统计：

SELECT TOP 20 
    wait_type,
    wait_time_ms / 1000 AS wait_time_sec,
    signal_wait_time_ms / 1000 AS signal_wait_sec,
    wait_time_ms - signal_wait_time_ms AS resource_wait_sec
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;

若存在PAGEIOLATCH_*（磁盘IO瓶颈）、RESOURCE_SEMAPHORE（内存不足）等高频等待，需针对性优化硬件或配置。

• 对比查询IO消耗：

SET STATISTICS IO ON;
GO
-- 执行EF生成的查询
GO
SET STATISTICS IO OFF;

SET STATISTICS IO ON;
GO
-- 执行修改后的查询
GO
SET STATISTICS IO OFF;

查看逻辑读、物理读的差异，确认是否存在索引缺失或碎片问题。

5. 极端场景排查 #

• 检查查询存储中的不良计划：若启用了查询存储，可在SSMS中通过「数据库→查询存储→顶级资源消耗查询」找到目标查询，选择性能优的计划并右键「强制计划」。
• 重建高碎片索引：

SELECT 
    OBJECT_NAME(ips.object_id) AS table_name,
    name AS index_name,
    avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE avg_fragmentation_in_percent > 30;

对碎片率超30%的索引执行重建：

ALTER INDEX ALL ON dbo.目标表名 REBUILD;

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