在超时时看到的存储过程的LATCH_EX和CX_PACKETS

Question

我正试图解决以下注意事项的问题：

有这个存储的proc，它会在一些随机的日子超时，而其他的日子或重试之后可能会更快地完成。暂停设置1小时。当它完成时，它会在15分钟内完成。

下面我们已经注意到这个DB服务器-

• 所讨论的数据库是一个LS备用只读数据库，它每隔4小时从主数据库恢复一次。
• 因此，SP驻留在辅助LS、只读数据库和用户查询上，一旦其可访问。
• 在恢复完成后的某些上午，我们看到这个SP被超时，在下面作为前2等待LATCH_EX和CXPACKETS (等待时间显示累积了8小时的1小时运行，可能是因为MAXDOP是8)。

然而，当我们在下一个数据库恢复周期后重试相同的proc时，相同的SP只在15分钟内完成。

当它完成的时候-

• 我们确实看到了CXPACKETS，但是等待时间非常短。
• 我们没有看到任何LATCH_EX等待

这里可能发生了什么，我如何进一步解决这一问题？我们已经共享了可以完成的代码级调优，但是DEVs更感兴趣的是为什么同一SP在15分钟内完成，失败可能是一周中的一天。

服务器级设置：

MAXDOP=8 CTOP=5

编辑--我们在SP中发现了一个特殊的语句，它往往是最慢的，花费了1小时时间的90%，在通过并行作业从多个表中选择数据之后，插入# (temptable)。

• 当存在问题时，除了CXPACKETS之外，上述语句还在等待LATCH_EX。
• 当运行更快时，CXPACKETS是存在的，但是没有LATCH_EX等待

Answer 1

CXPACKET等待最可能是由于索引扫描操作符(S)在SP的执行计划中，从特定的表(S)读取了很多行。索引扫描可能是并行的，一些线程正在等待其他线程的完成，因此CXPACKET等待类型。

可能发生的情况是，当SP完成时(不到15分钟)，执行计划是不同的，读取较少的行或包含较少索引扫描，读取大量行。当SP超时，您看到CXPACKET 8小时时，执行计划可能包含更多索引扫描操作符，这些操作符读取大量行

当你回答我在评论中发布的问题时，我可以分享更多信息。

无论如何，您必须捕获存储过程的执行计划，查看执行计划在成功时与超时时的区别。

解决方案是识别SP中导致使用索引扫描(S)读取大量行的问题部件(语句)，并重写它们以避免使用索引扫描(

)。

或者，您可以增加超时设置，或者获得更好的硬件。