我观察（并重现）了 SQL Server 2022 中的以下场景。
使用中的模式
- 代码通过 sp_executesql 执行（不涉及存储过程）
- 第一个查询将数据选择到临时表中
- 然后，DDL 语句会在临时表上创建聚集索引。临时表绝对不可缓存——首先，这不是一个模块（存储过程或函数），而且我们在临时表填充后创建索引。因此，我不希望缓存的临时对象上留下的统计信息在这里被涉及。
- 查询从临时表中选择数据。此查询每次都会获得完全优化（不是 TRIVIAL 计划）
该批处理可以针对小型和大型数据集运行，因此临时表中可以包含 1 行或数千行。
此行为通常发生在可读辅助数据库上。没有可写查询存储，也没有自动计划强制因素。
我已经验证我也可以针对主副本重现该行为。（自动计划更正被告知忽略查询，并且我确认重现时没有在主副本上强制执行计划。）
复制脚本
- 安装脚本- 我在 SQL Server 2022 CU15 上运行了此脚本。这会关闭查询存储并使用兼容级别 130。
- 重现查询- 我一直通过 SQL Query Stress 运行该查询，因此我可以轻松地在一个或多个线程上同时运行该查询
- 计划生成编号和临时表- 一个非常简单的查询，用于观察系统查询统计中的 plan_generation_num（“重新编译后可用于区分计划实例的序列号。”）和当前的临时表列表
通常会发生什么——以及我期望的行为
通常，在查询执行之间更改临时表中的大量行会自动导致重新编译，并且我会看到从临时表中选择数据的查询具有与临时表中的行匹配的行估计值。
当它按预期工作时，性能良好。
使用重现查询：如果我清除计划缓存，然后在 SQL 查询压力中的单个线程上运行重现查询 40 次迭代，plan_generation_number 最终为 82。当使用 sp_WhoIsActive 对查询计划进行采样时，查询临时表的行估计值与临时表中的行数匹配，正如预期的那样。
有时会发生的情况——在我看来像是一个错误
在极少数情况下，我看到正在使用的计划中，临时表的估计计划只有 1 行，但临时表中实际上有大量行。很多行已更改，但它没有自动重新编译：
这会导致性能非常缓慢，因为低估计计划决定使用没有预取的嵌套循环，这使其成为 CPU 消耗器。
使用重现查询：如果我清除计划缓存，然后在 SQL Query Stress 中的 2 个线程上运行重现查询 20 次迭代，则 plan_generation_number 最终会小于 82——它因运行而异，但可能是 72 或 59，表示重新编译次数较少。在运行时，我还可以使用 sp_WhoIsActive 对估计的行数为单个但临时表中的行数更多的情况进行采样。屏幕截图：
我只能在多个并发会话上运行重现代码时重现此问题
我无法在 SQL Server 中使用单个会话重现此行为。我能重现此行为的唯一方法是设置一个代码块：
- 执行至少 1 次 sp_executesql 语句的迭代，该语句在临时表中有 1 行
- 然后执行 sp_executesql 语句的 1 次迭代，该语句在临时表中包含更多行
如果我在单个会话中运行此程序，则无法重现问题。但如果我在四五个会话中同时运行此程序，则偶尔会出现“未按应有的方式重新编译”的问题。（注意：使用 SQL Query Stress，我只需 2 个会话/迭代即可重现此问题。）
我觉得这像是一个 bug，我很好奇是否有人见过它。不过，使用临时表重新编译和统计行为非常复杂，因此我可能忽略了它如何与不可缓存的临时表一起工作的一些细微差别。
PS：我确实认为可缓存的临时表通常更好。我只是想弄清楚为什么这种行为会在此时在不可缓存的临时表场景中发生。
解决方法
在查询中添加后option (recompile)，我无法再重现查询临时表的 1 行计划的重用。这已经足够了，但我很困惑为什么这是必要的。
设想
假设两个会话 A 和 B 大约同时执行您的重现。
它们都运行
sp_executesql一行的准备好的批处理。这里没有什么特别有趣的事情发生。然后，两个会话都会进入第二次执行，并且行数会大大增加。
假设会话 A 首先启动。照常，它使用缓存的执行上下文 (MXC) 或从缓存的计划 (CP) 派生出新的执行上下文。
请记住，CP 适用于整个批次，因此可能包含多个语句和可执行计划，每个语句和可执行计划都可以独立重新编译（语句级重新编译）。临时表在 CP 中按名称引用，因此可以重复使用。CP 是一种公共且可重入的结构。
MXC 是单个会话使用的 CP 的轻量副本，其中填写了特定执行的所有详细信息。这些详细信息包括局部变量和参数值、临时表 ID（不是名称）等。
MXC 就是您现在要吃的特定饼干，如果您愿意的话，它是以 CP 饼干切割器为模型的。
第二
同时，会话 B 也在第二个准备好的批处理执行中启动。它也使用缓存的 MXC（或派生出一个新的）。
回到会话 A。它以一种有趣但平淡无奇的方式创建了临时表和索引，然后继续运行
SELECT有问题的。会话 A 决定按顺序进行语句级重新编译，然后执行该操作，运行SELECT（具有准确的行数）并完成。会话 B 现在执行了大致相同的操作，但决定不需要重新编译。它继续使用基于单行情况的未修改的 MXC，您会看到问题显现。但为什么呢？
重新编译
嗯，
SELECT实际上不会因为更新的统计信息而重新编译。对于会话 A 或 B 来说，它永远不会重新编译。就它们而言，这些统计信息每次都是新的且未修改的。毕竟，这是一个新的且未缓存的临时表。现在，当然
SELECT，确实重新编译了，并且声明的原因表明“统计信息已更改”。解释是，即使没有找到有趣的统计信息（或者发现它们没有更改，如这里），SQL Server 仍会执行一次测试，然后决定不需要重新编译。最终测试是仅基于表中的总行数的“阈值跨越”。 引用自SQL Server 2005 中的批处理编译、重新编译和计划缓存问题：
会话 B 知道其本地临时表中有多少行。这远大于 1，那么为什么阈值测试不会导致重新编译？这是有原因的，但我需要花一两段话来解释。
细节
通过 MXC，可以同时执行多个 CP。这只是缓存计划同时从多个会话运行的常规情况。
如果每个会话都将表的已知当前行数与其私有 MXC 中的缓存计数进行比较，则会导致过多的重新编译。每次并发执行都会看到超出阈值，执行语句级重新编译，并替换 CP 中保存的当前语句计划。
更糟糕的是，每次重新编译时，最终语句的计划可能都相同，因为永久表的变化速度不会太快（无论如何，在单个语句级重新编译的时间范围内不会）。总的来说，对于非临时表，这种方案效率极低，而且浪费资源。
因此，第一个并发执行将执行重新编译，并使用新的基数值更新 CP。然后，其他并发执行将已知基数与此更新的值进行比较，因此不太可能导致进一步的语句重新编译。
此外，由于这是计划最优性重新编译（而不是计划正确性重新编译），其他执行将继续使用它们已有的缓存计划。下次它们将选择新的语句计划。
暂时不便
这种安排不太适合临时表，因为每个会话都有一个不同的公共 CP 临时表“副本”，并且行数实际上有很大差异。
让我们看看会发生什么：
会话 B 将其已知的临时表行数（大）与 CP 中存储的当前值进行比较。但是会话 A 在执行语句级重新编译后刚刚更新了该 CP 值（现在也很大）！
因此，会话 B 的 MXC 和共享 CP 的阈值数字匹配，因此未确定重新编译原因。您最终会得到一行估计计划（来自未重新编译的会话 B 的 MXC），在临时表的当前实例中遇到更多行。
不理想
现在，这可能不是一个理想的情况。
有人可能会争辩说，临时表（仅！）应该根据MXC 缓存值（而不是共享 CP）检查其已知的当前基数，因为没有其他会话可以影响其私有临时表。
好的，但是如果我们重新编译，我们该把新的语句计划放在哪里？在 CP 中替换单个共享版本可能是错误的，因为其他会话的临时表计数差异很大，可能需要不同的计划才能获得良好的性能。谁能说谁的临时表更好，更能代表常见情况？如果我们把新计划扔掉，我们只是在没有人要求我们的情况下应用了一种奇怪的重新编译提示。
这个难题不会出现在永久表中，因为只有一张永久表，并且具有相当静态的行数。
有人可能会争辩说，可以将会话 ID 添加到 CP 缓存键中，这样每个会话都会获得自己的定制计划，就像在引用它们的批处理之外创建的临时表一样。好吧，不用了，谢谢；这种情况已经产生了足够多的计划缓存污染。别忘了，这将适用于整个语句批处理，而不仅仅是棘手的语句批处理。
或者也许临时表应该将“最优性”重新编译视为“正确性”重新编译，这意味着它们将在 CP 中使用更新后的语句计划。也许，但它们没有这样做，而且临时表本身可能存在太多特殊情况。无论如何，它仍然可能最终成为参数敏感性问题的另一种形式。
结论
这是时间问题。一个会话更新 CP 共享阈值和重新编译的语句级计划，而另一个会话的缓存 MXC 阈值与更新值没有太大差异（在本例中根本没有差异）。这导致第二个会话无法以最佳方式重用其私有 MXC 中缓存的现有语句计划。
最好的解决方案似乎是在
OPTION (RECOMPILE)有问题的语句上添加本地提示，正如您已经发现的那样。是的，您最终会比严格必要的次数更频繁地重新编译该语句，但这也许比上述场景偶尔导致的性能灾难要好。在这种情况下，确实如此。重新编译时间很短，而且相比之下语句的运行时间也相当长。