几周或几个月一次，但总是在同一工作日，每日批处理作业中使用的存储过程中的一个查询可能会明显卡住并运行大约 200 分钟，直到应用程序终止其连接。这是一个非常简单的教科书查询，它连接 5 个表并计算sum()一个字段以分配给一个变量，因此在这里提供它没有意义。求和的记录总数一般为1-2打。这个查询没有参数，所以参数嗅探（幸运的是）是不可能的。
我看过很多东西：
- 数据库中没有在线用户。它纯粹用作数据转储。
- 没有同时运行的作业。数据库上的最后一个活动是大约提前 10 分钟完成的 tranlog 备份。下一个活动在大约 2 小时后开始，它是一个完整的备份，几分钟后就可以正常完成（虽然这个查询仍然很疯狂）。
此查询运行时有以下锁，均处于 GRANT 状态：
OBJECTSch-S锁定模式下请求的表之一HOBT.BULK_OPERATIONS在模式下锁定另一个表OBJECTIX锁定模式下请求的另一个表
该查询(NOLOCK)对所有连接表都有提示。它处于RUNNABLE状态，根据sp_whoisactive，它在服务器上每 15 分钟运行一次，相关查询的 CPU 计数不断增加。CPU 增长到独立查询执行的 1000 倍左右。来自的查询的 IO 与sp_whoisactive我独立运行它时大致相同，但读取计数非常大，是独立执行的 10000 倍。感觉就像查询处于无限循环中。但是，当我早上进入办公室时，作业会在几秒钟内重新运行。
报告的计划
sp_whoisactive与独立计划相同。当我运行它时，有一个创建索引的优化器提示，但由于查询独立时间约为 200 毫秒，我并不关心这个提示。实施它似乎又减少了 10 毫秒。DBCC CHECKTABLE在任何涉及的表或上都没有报告错误DBCC CHECKDB。
我对该计划的唯一怀疑是它有两个平行图标。但是添加option (maxdop 1)只会将其执行时间从 200 毫秒增加到 400 毫秒。
涉及到5张表，大小不一，最大的大概有1*10^6条记录，不算大。我们每天凌晨 4 点刷新统计数据和索引，作业在午夜运行，然后再导入任何大量数据。作业本身只添加 1-10 条记录。
强制为挂起的查询存储执行计划会导致相同的结果：查询卡住、巨大的 CPU 和 IO。让优化器发挥它的魔力，或者强制我获得独立的计划（它们是相同的）它在瞬间完成。
...
长话短说：我被允许在此作业的“正常”执行时间内在生产中运行每晚计划的跟踪，以尝试解决此问题。我们不知道下一次发生的时间，只希望它会遵循我们观察到的模式——每隔几周。
我的百万美元问题是我应该追踪哪些事件？我只是一名 SQL 开发人员，没有典型的 DBA 技能，所以我不知道应该通过跟踪暴露 SQL Server 的哪些内部结构以抓住罪魁祸首。
根据您的详尽概述，我相信您的表统计信息可能存在问题。
表仅在通过某些行更新阈值时自动更新其统计信息，对于任何超过 500 行的表，它需要 500+20% 的行进行更改。例如，您的百万行表需要 200,500 行更改才能更新统计信息。
索引
REBUILD将更新表的统计信息（REORGANIZE不是）。我认为您的表会随着时间的推移而增长并最终使您的统计信息无效，但不足以触发自动更新。
REBUILD但是，在您的查询由于当天早些时候的最后几项更改而开始执行不佳之前，您的重建索引作业不会触发相关表的实际操作。如果您的更改不一致且增量足够小，这将使性能下降一致且突然。在查询运行不佳并且第二天早上一切看起来都很好之后，您的重建索引工作就会解决问题。需要检查以确认的事项：
选择名称作为 index_name ,STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated 来自 sys.indexes WHERE OBJECT_ID = OBJECT_ID('SchemaName.TableName')如果您运行的是 SQL Server 2008 R2 Service Pack 1 或更高版本，您可以选择启用记录的跟踪标志 2371以更频繁地动态更新统计信息：
对于兼容级别 >= 130 的数据库，从 SQL Server 2016 开始默认启用新行为（即没有跟踪标志）。
您是否尝试过使用以下任一查询提示运行查询：
KEEP PLAN或KEEPFIXED PLAN？一旦有了正确的执行计划，
KEEPFIXED PLAN就永远不会更改计划，除非基础架构发生更改或在存储过程上运行sp_recompile 。KEEP PLAN只是放宽了重新编译阈值（由于统计数据的变化）。请注意
KEEPFIXED PLAN，在很长一段时间后，可能会保留一个不足以更改基数的执行计划。如果查询中涉及的表的统计数据随时间变化不大（即表的基数和统计数据变化不大），并且问题是一个真正的问题，我可能会看看是否使用fixed plan 是一个长期的解决方案，只要简单地尝试很长时间。它要么在某个时候再次中断（该计划开始对基础数据不利），我将开始寻找另一种解决方案。如果它没有破裂，那么对我来说就足够了。