sp_execute_remote偶发无返回的原因、检测修复及远程诊断方案咨询

• 弹性查询TDS流偶发异常：同一区域Azure内部网络虽可靠性极高，但偶发的TCP重传与TDS协议默认超时阈值不匹配会导致会话挂死，sp_execute_remote未触发超时的场景下会进入无限等待，手动执行时走新会话所以返回正常。
• 远程执行计划缓存参数嗅探：你手动执行捕获的@stmt时是全新会话，生成的执行计划和故障时老会话的缓存计划存在差异，老计划遇到了短暂的行锁/页闩阻塞，DBA排查时阻塞已经自动解除，但调用侧会话因未设置超时仍保持等待状态。
• 全局临时表隐式元数据锁冲突：你使用的##newrecs是全局临时表，即使没有进程显式锁表，同一时间如果有其他会话遍历系统表查询临时表元数据，会产生短暂的元数据共享锁冲突，sp_execute_remote返回结果写入临时表时被阻塞，排查时冲突已经消失但会话已经挂死。
• 数据库作用域凭据缓存刷新异常：凭据缓存偶发刷新失败会导致远程认证阶段静默挂死，不会抛出认证错误也不会推进执行流程，手动执行时走新的凭据缓存所以运行正常。

• 显式设置查询超时：在sp_execute_remote执行前运行EXEC sp_set_session_context N'REMOTE_DATA_ARCHIVE_QUERY_TIMEOUT', <超时秒数>，或者添加SET LOCK_TIMEOUT <超时毫秒数>配置，超时后自动终止会话抛出错误，ADF侧捕获错误后直接重试即可。
• 增加心跳检测逻辑：在存储过程中增加定时写入专属心跳表的逻辑，ADF侧定期查询心跳表的最后更新时间，超过阈值就kill对应会话触发重试。
• 替换临时表级别：如果不需要跨会话共享##newrecs的数据，将其替换为会话级临时表#newrecs，完全规避全局临时表的元数据锁冲突问题。
• 添加执行计划重编译提示：在@stmt语句末尾加OPTION (RECOMPILE)，避免参数嗅探导致的异常执行计划。

• 查询系统动态视图历史快照：无需修改生产配置，只需DBA开放sys.dm_exec_query_stats、sys.dm_exec_sessions、sys.dm_os_wait_stats的只读查询权限，筛选故障时间点对应你所用只读账号的会话记录，查看等待类型即可定位根因：如果是NETWORK_IO、PREEMPTIVE_OS_AUTHORIZATIONOPS类等待对应网络/认证问题，如果是PAGEIOLATCH_*、LOCK_*类等待对应执行阻塞问题。
• 排查Azure Monitor内置指标：无需登录远程数据库，直接查看远程库故障时间点的CPU、IO、会话数、死锁数指标，判断是否存在资源瓶颈，也可查看SQL审核日志的执行记录，确认故障时的远程查询是否在远端实际执行完成并返回结果。
• 访问Query Store历史数据：Query Store默认会留存所有查询的执行历史，无需开启任何新的trace，仅需DBA开放Query Store的只读权限，筛选你执行的@stmt对应的历史执行计划和耗时，即可对比故障时间点的执行和正常执行的差异。

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