SQL Server 2014的MAXDOP设置

Question

我知道这个问题被问了很多次，也有答案，但是，我仍然需要在这个问题上有更多的指导。

下面是我来自SSMS的CPU的详细信息：

下面是DB Server任务管理器中的CPU选项卡：

我将MAXDOP设置为2，如下公式所示：

declare @hyperthreadingRatio bit
declare @logicalCPUs int
declare @HTEnabled int
declare @physicalCPU int
declare @SOCKET int
declare @logicalCPUPerNuma int
declare @NoOfNUMA int
declare @MaxDOP int

select @logicalCPUs = cpu_count -- [Logical CPU Count]
    ,@hyperthreadingRatio = hyperthread_ratio --  [Hyperthread Ratio]
    ,@physicalCPU = cpu_count / hyperthread_ratio -- [Physical CPU Count]
    ,@HTEnabled = case 
        when cpu_count > hyperthread_ratio
            then 1
        else 0
        end -- HTEnabled
from sys.dm_os_sys_info
option (recompile);

select @logicalCPUPerNuma = COUNT(parent_node_id) -- [NumberOfLogicalProcessorsPerNuma]
from sys.dm_os_schedulers
where [status] = 'VISIBLE ONLINE'
    and parent_node_id < 64
group by parent_node_id
option (recompile);

select @NoOfNUMA = count(distinct parent_node_id)
from sys.dm_os_schedulers -- find NO OF NUMA Nodes 
where [status] = 'VISIBLE ONLINE'
    and parent_node_id < 64

IF @NoofNUMA > 1 AND @HTEnabled = 0
    SET @MaxDOP= @logicalCPUPerNuma 
ELSE IF  @NoofNUMA > 1 AND @HTEnabled = 1
    SET @MaxDOP=round( @NoofNUMA  / @physicalCPU *1.0,0)
ELSE IF @HTEnabled = 0
    SET @MaxDOP=@logicalCPUs
ELSE IF @HTEnabled = 1
    SET @MaxDOP=@physicalCPU

IF @MaxDOP > 10
    SET @MaxDOP=10
IF @MaxDOP = 0
    SET @MaxDOP=1

PRINT 'logicalCPUs : '         + CONVERT(VARCHAR, @logicalCPUs)
PRINT 'hyperthreadingRatio : ' + CONVERT(VARCHAR, @hyperthreadingRatio) 
PRINT 'physicalCPU : '         + CONVERT(VARCHAR, @physicalCPU) 
PRINT 'HTEnabled : '           + CONVERT(VARCHAR, @HTEnabled)
PRINT 'logicalCPUPerNuma : '   + CONVERT(VARCHAR, @logicalCPUPerNuma) 
PRINT 'NoOfNUMA : '            + CONVERT(VARCHAR, @NoOfNUMA)
PRINT '---------------------------'
Print 'MAXDOP setting should be : ' + CONVERT(VARCHAR, @MaxDOP)

我仍然看到与CXPACKET相关的高等待时间。我正在使用下面的查询来获得这个结果：

WITH [Waits] AS
(SELECT
[wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT',
N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
AND [waiting_tasks_count] > 0
)
SELECT
MAX ([W1].[wait_type]) AS [WaitType],
CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
MAX ([W1].[WaitCount]) AS [WaitCount],
CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- percentage threshold
GO

目前，我的服务器的CXPACKET等待时间是63%：

我参考了多篇关于专家推荐的文章，并查看了MAXDOP 微软的建议；然而，我并不确定这个建议的最佳值应该是什么。

我找到了一个关于同一个话题的问题，这里，但是如果我同意Kin的建议，那么MAXDOP应该是4，在同一个问题中，如果我们使用Max，它应该是3。

请提供您的宝贵建议。

版本: Microsoft 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) 2018年9月7日01:37:51企业版:基于核心的授权(64位)在WindowsNT6.3(Build9600：) (Hypervisor)上

并行性的成本阈值设置为70。在测试了从默认值到25和50之间的相同值之后，CTfP被设置为70。当默认(5)和MAXDOP为0时，CXPACKET的等待时间接近70%。

我在专家模式下执行了60秒的sp_blitzfirst，下面是查找结果和等待状态的输出：

Answer 1

[医]伪

以下是为什么这个等待状态报告会发臭:它不能告诉您服务器已经运行了多长时间。

我可以在你的CPU时间截图中看到: 55天！

好吧，我们来算一算吧。

数学

每天有86,400秒。

SELECT (86400 * 55) seconds_in_55_days

答案是什么？4,752,000

您总共有452,488秒的CXPACKET。

SELECT 4752000 / 452488 AS oh_yeah_that_axis

这给了你..。10 (如果你做实际的数学，这里接近9.5 )。

因此，虽然CXPACKET可能占服务器等待量的62%，但它只发生了大约10%的时间。

，别管它，

您已经对设置进行了正确的调整，如果您想要以有意义的方式更改数字，就应该进行实际的查询和索引优化。

其他考虑事项

CXPACKET可能是由倾斜的并行性引起的：

• 关于CXPACKET等待:倾斜并行性的更多信息

在较新的版本中，它可能以CXCONSUMER的形式出现：

• 中科院是无害的吗？别那么快，老虎。

如果没有第三方监控工具，可能值得自己捕获等待状态：

• 捕获一段时间的等待统计信息
• 如何用sp捕获基线_BlitzFirst

Answer 2

等等，统计数据只是数字。如果您的服务器正在执行任何操作，那么很可能会出现某种等待。此外，根据定义，必须有一个等待，这将有最高的百分比。如果没有某种正常化，那并不意味着任何事情。如果我正确地读取任务管理器的输出，您的服务器已经运行了55天。这意味着总体上只有452000/(55*86400) = 0.095秒的CXPACKET每秒。此外，由于您在Server 2014上，所以您的CXPACKET等待包括良性的并行等待和可操作的等待。有关更多详细信息，请参阅使并行性等待行动。我不会得出一个结论，即基于您在这里介绍的内容，MAXDOP设置错误。

我首先要测量吞吐量。这里真的有问题吗？我们不能告诉你怎么做，因为这取决于你的工作量。对于OLTP系统，您可以测量每秒的事务。对于ETL，您可以测量每秒加载的行，依此类推。

如果您确实有问题，并且需要改进系统性能，那么当您遇到这个问题时，我会检查CPU。如果CPU太高，那么您可能需要调优查询、增加服务器资源或减少活动查询的总数。如果CPU太低，那么您可能再次需要调优查询，增加活动查询的总数，或者可能有一些等待类型负责。

如果您确实选择查看等待状态，则应该只在您遇到性能问题的期间查看它们。纵观过去55天的全球等待统计数据，几乎在所有情况下都不可能采取行动。它增加了不必要的噪音数据，使你的工作更加困难。

一旦您完成了适当的调查，更改MAXDOP可能会对您有所帮助。对于您这样大小的服务器，我将坚持使用MAXDOP 1、2、4或8，我们不能告诉您其中哪一个最适合您的工作负载。您需要在更改MAXDOP之前和之后监视您的吞吐量，以得出结论。

Answer 3

1. 您的“启动”maxdop应该是4个；每个numa节点的最小核数最多可达8个。您的公式不正确。
2. 等待特定类型的百分比很高并不意味着什么。SQL中的所有内容都在等待，所以某些东西总是最高的。唯一高的cxpacket等待意味着您有一个高比例的并行性。CPU总体上看起来并不高(至少对于提供的快照来说是这样)，所以可能没有问题。
3. 在试图解决问题之前，先定义问题。你想解决什么问题？在这种情况下，您似乎已经将问题定义为高百分比的cxpacket等待，但这本身并不是一个问题。