Sql-Server

SQL Server 2014 的 MAXDOP 設置

  • March 30, 2019

我知道這個問題已經被問過很多次並且也有答案，但是我仍然需要關於這個主題的更多指導。

以下是來自 SSMS 的我的 CPU 的詳細資訊：

中央處理器

下面是 DB 伺服器任務管理器中的 CPU 選項卡：

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 Vernon，它應該是 3。

請提供您寶貴的建議。

版本：Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 Enterprise Edition：Windows NT 6.3 (Build 9600:) 上基於核心的許可（64 位）（管理程序） )

並行度的成本門檻值設置為 70。CTfP 已設置為 70，在測試了從預設值分別為 25 和 50 的相同值之後。當它是 default(5) 並且MAXDOP為 0 時，等待時間接近 70% CXPACKET。

我sp_blitzfirst在專家模式下執行了 60 秒，下面是結果和等待統計的輸出：

sp_blitzfirst

虛假

這就是為什麼等待統計報告很糟糕的原因：它沒有告訴你伺服器已經執行了多長時間。

我可以在你的 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 可能來自傾斜的並行性：

在較新的版本上，它可能顯示為 CXCONSUMER：

如果沒有第三方監控工具，可能值得自己擷取等待統計資訊：

引用自：https://dba.stackexchange.com/questions/232943

相關問答

Sql-Server

SQL Server 的 MAXDOP 設置算法

  • January 19, 2022
檢視問答
Sql-Server

查詢執行時使用了多少個執行緒？

  • July 1, 2020
檢視問答
Sql-Server

為 SQLserver 2016 及更高版本設置 MAXDOP

  • September 13, 2019
檢視問答
Sql-Server

如何從使用 SSDT 14.06 在 VS 2015 中創建的部署腳本中刪除 Alter、Drop、Create SQL

  • August 29, 2019
檢視問答
Sql-Server

增加 SQL Server 生產上的最大記憶體設置

  • June 20, 2019
檢視問答
Sql-Server

多實例伺服器上的 SQL Server 配置

  • March 27, 2019
檢視問答