EF6调用Azure SQL DB存储过程偶发直至连接超时才返回数据问题求助

该问题是典型的**SQL Server参数嗅探（Parameter Sniffing）**问题，完全匹配所有描述的异常特征：

• 存储过程首次执行时，会根据当时传入的参数值生成并缓存查询计划
• 如果首次传入的参数对应的结果集量级特殊（比如某营养师名下有数千个客户，普通营养师仅几十位），缓存的执行计划就会适配这个特殊参数，后续其他普通参数复用该计划时，就会出现性能断崖式下跌
• SSMS执行时默认开启了ARITHABORT等会话参数，和EF发起的连接会话参数不一致，因此SSMS会生成新的执行计划，不会复用EF的坏缓存，所以你在SSMS内执行永远能保持稳定性能
• 重新编译存储过程会清空缓存的坏计划，因此问题暂时消失，直到下一次特殊参数触发坏计划缓存就会复现
• 该异常由SQL端计划缓存触发，和应用部署环境无关，因此本地调试也能复现

优先推荐方案：参数复制（零额外开销，无业务侵入） #

在存储过程开头把输入参数赋值给本地变量，后续查询全部使用本地变量，从根源避免参数嗅探，不需要每次查询重编译，性能损失可忽略：

ALTER PROCEDURE [PORTAL].[GetNutritionistCustomers]
    @id_usernut varchar(20)
AS
-- 新增参数复制逻辑
DECLARE @local_id_usernut varchar(20) = @id_usernut

WITH Activity AS (
    SELECT LastActivity = MAX(ExecutionDate),
           ded.UserName
    FROM portal.DailyExecutionData ded
    WHERE ded.ExecutionDate < GETDATE()
    GROUP BY ded.UserName
),
Logging AS (
    SELECT LastLogin = MAX(l.LoggedOnDate),
           l.UserName
    FROM PORTAL.Logins l
    WHERE l.LoginType = 'Direct Client'
    GROUP BY l.UserName
)
SELECT unc.CompanyCode,
       a.ACCOUNT,
       ad.ADDRESS1,
       un.ID_UserNut,
       ueu.ExpirationDate,
       Expired = CAST(CASE WHEN ueu.ExpirationDate < GETDATE() THEN 1 ELSE 0 END AS BIT),
       LastActive = la.LastActivity,
       l.LastLogin
FROM UK_Nutritionist un
JOIN UK_NutCompany unc ON un.ID_UserNut = unc.ID_UserNut
JOIN UK_EnabledUsers ueu ON unc.CompanyCode = ueu.UserName
JOIN Klogix.ACCOUNT a ON ueu.ID_User = a.ACCOUNTID
LEFT JOIN Klogix.ADDRESS ad ON a.ADDRESSID = ad.ADDRESSID AND ad.IsPrimary = 1
LEFT JOIN Activity la ON ueu.UserName = la.UserName
LEFT JOIN Logging l ON ueu.UserName = l.UserName
-- 替换为本地变量
WHERE un.ID_UserNut = @local_id_usernut

该方案不需要修改应用代码，仅需调整存储过程，可解决90%以上的同类参数嗅探问题，也不会产生全量重编译的额外开销。

次选方案：语句级重编译提示 #

如果不想调整参数逻辑，可以仅在最终SELECT语句末尾添加OPTION (RECOMPILE)，仅针对该查询每次生成适配当前参数的新计划，开销远低于存储过程全局重编译，也可以解决问题。

验证方法 #

问题复现时可执行以下语句查询Azure SQL的计划缓存，实锤参数嗅探问题：

SELECT 
    p.usecounts, p.cacheobjtype, p.objtype, 
    t.text, s.total_worker_time, s.total_elapsed_time
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t
JOIN sys.dm_exec_query_stats s ON p.plan_handle = s.plan_handle
WHERE t.text LIKE '%GetNutritionistCustomers%'

如果查询结果中出现usecounts很高但total_elapsed_time异常的计划，即可确认是参数嗅探导致的坏计划复用问题。

内容的提问来源于stack exchange，提问作者Piers Cañadas