sql-server - 并行性会导致 100% 的 CPU 使用率吗？

Question

我意识到这个问题有点含糊，但我希望有人能指出我要研究的方向。

我的问题是我在一个程序中有一个查询将我的 SQL Server 上的 CPU 驱动到 100%。我认为问题与并行性有关，但我不知道如何证实我的怀疑，或者解决问题的最佳方法。

有问题的查询花费了大约 20 分钟的时间以 100% 的 CPU 运行服务器，此时我终止了查询。我使用 sys.dm_exec_query_stats 查看查询执行计划。没有索引扫描。索引操作都是查找。我相信我的索引是为这个查询精心挑选的。我确实看到查询中发生了很多并行性。

为了比较，我尝试在 Management Studio 的查询窗口中运行相同的查询。查询运行不超过几秒钟，显示估计的执行计划按钮为我提供了不同的执行计划。此执行计划的不同之处之一是没有并行性。

Here is a link to the query execution plan for the bad query.有一件事看起来像一个问题是聚集索引在 PS_TRANSACTION_INV 上查找，谓词在 PS_TRANSACTION_INV.BUSINESS_UNIT 上(在执行计划中一直到右边)显示 1估计行。除了几百行之外的所有行都具有值 TRUS1，其余行具有值 TRCN1。该表有大约 1500 万行并使用大约 22 GB(因此行非常大)。我为表的聚集索引做了 DBCC SHOW_STATISTICS，统计信息看起来是正确的(如下所示)。执行计划还估计行大小为 54 B，这是错误的。真正的行大小更大。

但是，即使有一个糟糕的执行计划，我也不明白这个查询怎么会运行 24 小时以上而没有完成。

查看 sys.sysprocesses，我可以看到最后一个等待类型是 SOS_SCHEDULER_YIELD。

Here is a link to the query execution plan for the good query.

我的服务器有 8 个 CPU 和 24 GB 内存。查询本身返回大约 27,000 行，因此它应该在服务器快速执行的能力范围内。这个程序已经运行了很多年，没有出现任何问题，所以我认为执行计划一定发生了一些变化。我的服务器的 DOP 是 64。

我是否认为问题可能与并行性有关？如果是这样，这怎么会导致本应在一两秒内运行的查询使用 100% 的 CPU 超过 20 分钟，但仍未完成？

编辑:在错误查询的执行计划中，我发现其中一个步骤是在理想情况下应该进行聚簇索引查找的字段上进行索引查找。索引查找是在一个大约有20,000,000行的表上进行的，但是索引字段具有很高的选择性。该索引搜索提供给查询计划中的嵌套循环(具有并行性)。我仍然认为问题在某种程度上与并行性有关。

作为引用，我可以使用非聚集索引对该表运行 SELECT，查询需要几秒钟。

Answer 1

我的第一个问题是您看到了哪些等待类型？

其次，您的服务器是如何设置的(也许运行 sp_Blitz 并为我们提供输出 sp_Blitz @OutputType = ‘markdown’, @CheckServerInfo = 1)？

第三，有没有其他东西在运行，造成瓶颈？也许从 sp_BlitzFirst 开始或 sp_whoisactive看看发生了什么。

Can parallelism cause 100% CPU usage?

当然可以。 Brent wrote a query to do just this thing .或者，至少，他使用 MAXDOP 0 来确保它更快地发生？

For comparison, I tried running the same query in the query window in Management Studio. The query runs in no more than a few seconds, and the Display Estimated Execution Plan button gives me a different execution plan

我不为布伦特工作，but he talks about this too. .您需要使用实际的执行计划，而不是估计的。 Adding it here , 并在您的帖子中包含该链接很有帮助。此外，您还需要确定应用程序是否正在使用缓存的过程计划，或者它们是否都在使用新计划。您可以使用 option(recompile) 强制执行新计划并将苹果与苹果进行比较(但就像 red delicious 与 granny smith 相比，它比苹果与橙子更好)

The query itself returns about 27,000 rows, so it should be well within the capability of the server to execute this quickly.

这么小的最终行数不应该用来衡量其性能不佳的原因。在评估最终谓词之前，在 N 个连接数中的某处可能有数百万行被拉回。这是实际执行计划会向您显示瓶颈的地方。

My server's DOP is 64

看起来很高，但您并没有告诉我们您运行的内核数量。幸运的是，Microsoft has good guidance on this. .在服务器级别更改它之前，将查询提示添加到查询的底部... option(maxdop 8)。此外，如果是并行性，我希望看到 CXPACKET 等待。 Here's a video on that. .

最后，记住 query optimization is hard ，甚至对于服务器。当然，这对您来说不是这种情况，因为您的应用程序会永远占用时间，并且 SSMS 会快速返回数据，这是我希望看到 ASYNC_NETWORK_IO 等待或其他内容的地方。