MAXDOP 和有趣的性能體驗

我想收到關於以下主題的建議。

使用我們公司的一種產品，我們“連接”到客戶方面的現有數據庫，並將訂單導入我們自己的 SQL Server，讓我們的桌面產品計劃和優化它們，以適應不同的卡車，這些卡車將採取不同的路線從A 到 B。在導入過程中，我們遇到了一些性能問題，可以通過以下方式解決：

此外，我不得不說，我從之前在這家公司工作的另一個人那裡接過了責任，擔任意外 DBA，但現在這是我的工作 :-)

我們已經制定了幾個維護計劃（據我所知，這並不總是最好的方法，我們現在遵循 Ola Hallengren 的 MaintenanceSolution）來重建索引和更新統計資訊。但是性能問題仍然存在。

前 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-Usage。看起來 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-Usage 與執行腳本前一樣高。

我對你們的問題是：

您是否知道為什麼在 SSMS 中切換 MAXDOP 會使 CPU 使用率降至正常值？

我知道我不能指望一個完全令人滿意的答案，因為我對那個故事的細節不夠詳細。但是，如果您能指出我正確的方向，例如嘿，看看 I/O，像這樣設置您的 PerfMon，等等，我將不勝感激。

提前謝謝你，克里斯托夫

進行更改時發生了一些事情。

當進行此更改時，SQL Server 使計劃記憶體中的所有執行計劃都過期。當這種情況發生時，正在執行的每個查詢 SQL Server 都必須生成新的計劃。

當您將 MAXDOP 設置從 0 更改為 1 時，這只會阻止 SQL Server 能夠使用並行執行計劃。並行計劃意味著 SQL Server 可以將所有處理器用於單個查詢。當您將此設置設置為 1 時，這意味著每個查詢只能使用一個執行緒。

CX_PACKET 等待類型只是意味著並行查詢的一個執行緒正在等待該並行查詢的另一個執行緒完成。CX_PACKET 是症狀而不是原因。

要確定根本原因，您需要查看問題查詢的執行計劃並了解問題所在。你可能缺少索引，你可能有過時的統計數據，你可能需要更多的 RAM，你可能有慢速硬碟，等等。

引用自：https://serverfault.com/questions/499635