☰

MAXDOP 和有趣的性能体验

tag-icon • tag-icon sql-server performance
MAXDOP 和有趣的性能体验

我希望收到有关以下主题的建议。

使用我们公司的一款产品，我们可以“连接”到客户端现有的数据库，并将订单导入我们自己的 SQL Server，以便我们的桌面产品可以针对不同的卡车规划和优化订单，这些卡车将按照不同的路线将货物从 A 运送到 B。在导入过程中，我们遇到了一些性能问题，可以通过以下方式解决：

此外，我必须说，我从之前在这家公司工作的其他人手中接过了责任，担任了意外 DBA，但现在这是我的工作 :-)

我们制定了几个维护计划（据我所知，这并不总是最好的方法，我们现在遵循 Ola Hallengren 的维护解决方案）来重建索引和更新统计数据。但性能问题仍然存在。

前任 DBA 尝试诊断 SQL Server 上发生的情况，并使用了以下脚本：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT 'Identify what is causing the waits.' AS [Step01];
SELECT TOP 10
        [Wait type] = wait_type,
        [Wait time (s)] = wait_time_ms / 1000,
        [% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0 / SUM(wait_time_ms)     OVER())
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
ORDER BY wait_time_ms DESC;

他发现CXPACKET的价格一直比较高。

他谷歌了一下，发现 MAXDOP 参数与此有关。因此他们转到 SSMS，将 MAXDOP 从一个值“0”更改为另一个值“1”，反之亦然。只要更改参数（无论朝哪个方向），性能就会立即提高。在切换参数之前，CPU 非常高（几乎无法使用该产品），切换后，CPU 使用率立即降至最低。看起来 SQL Server 已经无聊透顶了。

如果我们的人尝试将 MAXDOP 切换为每个脚本的其他值：

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max degree of parallelism', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

令人惊讶的是，它根本没有显示任何效果。CPU 使用率与执行脚本之前一样高。

我要问你们的是：

您是否知道为什么切换 SSMS 中的 MAXDOP 会导致 CPU 使用率降至正常值？

我知道我无法期待一个完全令人满意的答案，因为我对这个故事的细节还不够详细。但是如果您能给我指出正确的方向，我将不胜感激，例如，嘿，看看 I/O，像这样设置您的 PerfMon，等等。

提前谢谢你，克里斯托夫

答案1

当做出这一改变时，发生了一些事情。

进行此更改后，SQL Server 会使计划缓存中的所有执行计划过期。发生这种情况时，正在运行的每个查询 SQL Server 都必须生成新计划。

当您将 MAXDOP 设置从 0 更改为 1 时，这只会阻止 SQL Server 使用并行执行计划。并行计划意味着 SQL Server 可以将所有处理器用于单个查询。当您将此设置设置为 1 时，这意味着每个查询只能使用一个线程。

CX_PACKET 等待类型仅表示并行查询的一个线程正在等待该并行查询的另一个线程完成。CX_PACKET 是一种症状，而不是原因。

要确定根本原因，您需要查看问题查询的执行计划并了解问题所在。您可能缺少索引，统计数据可能已过期，可能需要更多 RAM，硬盘可能速度较慢，等等。

答案2

根据我的经验，CXPACKET 对于年轻的 DBA 来说总是一种棘手的等待类型，经常会导致一些可预见的错误反应。我尝试过对 SQL Server 中的 CXPACKET 等待类型进行故障排除本文介绍了 CXPACKET 价格高企的大部分原因，同时也解释了 CXPACKET 的背景

因此，对于那些不想了解详细信息的人，我将在这里发布文章摘要：

  • 不要将 MAXDOP 设置为 1，因为这永远不是解决方案

  • 调查查询和 CXPACKET 历史记录，以了解并确定这是否是只发生过一次或两次的事情，因为它可能只是正常 运行的
    系统中出现的异常

  • 检查查询使用的表的索引和统计信息，并确保它们是最新的

  • 检查并行度成本阈值 (CTFP)，并确保使用的值适合您的系统

  • 检查 CXPACKET 是否带有 LATCH_XX（可能还带有 PAGEIOLATCH_XX 或 SOS_SCHEDULER_YIELD）。如果是这种情况，
    则应降低 MAXDOP 值以适合您的硬件

  • 检查 CXPACKET 是否伴有 LCK_M_XX（通常伴有 IO_COMPLETION 和 ASYNC_IO_COMPLETION）。如果是这种
    情况，则并行性不是瓶颈。对这些
    等待统计数据进行故障排除，以找到问题的根本原因和解决方案

答案3

当 MAXDOP 设置为 0 时，SQL Server 将使用所有可用处理器来执行查询。当您将 MAXDOP 值更改为 1 时，您已限制 SQL Server 仅对单个查询使用 1 个进程/线程。

通常，当 MAXDOP 设置为 0（所有可用处理器）或设置为大于 1（例如 4）时，您将看到 CX_PACKET 等待类型。在这种情况下，查询将使用 4 个进程并行执行，当一个进程/线程等待另一个进程/线程完成时，您将观察到 CX_PACKET 等待类型。

要了解有关 MAXDOP 的更多信息，请阅读本文，其中解释了最大DOP。

相关内容