sql-server - SQL Server计划缓存突然发生异常

Question

目前，我正在进行浸泡测试，并不断遇到一个以前从未见过的棘手问题。我花了相当长的时间调查这个问题，但到目前为止还没有深入了解这个问题。

在测试过程中的某个时间点（有时是1小时，有时是4小时以上），SQL Server计算机开始将其CPU最大化。这总是与数据库高速缓存内存的急剧减少和空闲内存的增加相对应。

这些迹象显然指向内存压力，似乎我有时可以通过运行特别重的查询来触发此事件。

我可以理解为什么要刷新计划缓存，但是令我感到困惑的方面是：


刷新计划缓存并完成我的复杂查询后，有足够的可用内存（即使在进一步增加允许的SQL Server内存量之后），计划缓存似乎也无法恢复。我剩下的大量可用内存对任何人都没有帮助。
如果我停止浸泡测试，然后立即重新运行它，则一切恢复正常，计划缓存将按预期增长。 SQL Server不需要重新启动或更改任何设置。


缓存刷新后，缓存命中率仍然可以，约为90％，但是比刷新前看到的约99％低得多，这确实伤害了CPU。

在刷新之前，高速缓存未命中，插入和命中的痕迹看起来很正常。预刷新我看到的唯一问题是非参数化的即席查询，该查询非常频繁地插入到缓存中，尽管如此，这是一个非常简单的查询，成本低廉，因此希望它们能从前面的缓存中刷新大多数其他事情。

刷新后，我看到大量插入，紧接着是同一对象（即存储过程）上的许多未命中，因此缓存的内存消耗仍然很低。

从下面我的计数器截图中的黄线可以看到，缓存使用率下降并且保持较低，而可用内存（宝蓝色）仍然保持较高。



编辑

在继续研究此问题的同时，一种不断出现的模式是，如果我在短时间内将服务器推至极限（将负载添加到浸泡测试所产生的负载之上），则SQL Server似乎陷入混乱。它本身无法恢复。

当服务器达到最大压力时，与服务器的连接数量急剧增加（我认为是由于它无法足够快地处理请求，因此需要新的连接来处理“恒定”的请求流） 。然后，此积压将使服务器似乎无法从中恢复，从而进一步施加压力。

现在，我仍然对指标感到困惑。如果新连接似乎正在消耗内存，进一步减慢处理速度，导致新连接等，我可以将其纯粹视为服务器资源问题。我所看到的是虽然有足够的可用内存，但是SQL Server没有将其用于计划缓存。因此，它花费了更多的时间进行编译，CPU数量增加，并且事情变得一发不可收拾。

感觉连接是此问题的关键部分。如前所述，如果重新启动测试，一切都会恢复正常。从那以后，我发现将数据库置于单用户模式几秒钟，以便所有与测试相关的连接都消失，等待几秒钟然后返回多用户模式即可解决此问题。我已经尝试过杀死所有基于SPID的活动连接，但是似乎需要暂停几秒钟，服务器才能恢复并正确开始使用计划缓存。

请参阅下面我的计数器的屏幕截图。我正在尝试将服务器从顶部推至〜02：33：15，并在〜02：34：30设置为单用户模式，然后在几秒钟后设置为多用户模式。

紫色线表示用户连接，粗线红色表示编辑p / s，鲜绿色表示高速缓存，水色连接表示内存，灰/棕色表示空闲存储。

Answer 1

好的，这是一条漫长的循环之路，但是我目前对此的最佳答案是这个问题是由于资源限制以及SQL Server在我的特定情况下相对于计划缓存所做的不幸选择。我并不是说SQL Server是错误的，只是因为目前我的需求不正确。

我已经调整了浸泡测试，以便如果数据库服务器受到压力，它将稍微拉扯一下并丢弃一些连接，直到服务器恢复控制并可以重新建立其他连接为止。 SQL Server恢复自身状态的过程可能需要几分钟，但确实会发生！

服务器似乎陷入了一个恶性循环，承受着压力，它删除了缓存的计划，然后不得不花更多的时间在重新编译这些计划上，而不是首先删除这些计划。这导致事情变得一发不可收拾，一切都停滞不前。

在我的特殊情况下，缓存命中率非常高（高于99.5％），并且由于浸泡测试对于用户负载而言，重复进行数小时的重复操作基本上可以很好地利用缓存。如果缓存使用得不好，那么SQL Server可能会通过删除计划来做出正确的选择，但我认为这样做不是正确的。