SQL SERVER CXPACKET-Parallelism Wait Type 的慣用解決方案

標籤：blog   http   os   io   使用   strong   ar   for   資料   

最近我的兩個庫出現，出現較多的CXPACKET等待，在網上找了一下資料。其中有篇一個SQL Server專欄作家的文章不錯，也解決了我的一些疑問，就翻譯在這裡。

  翻譯整理僅用於傳播資訊之目的。

  原文出處：http://blog.sqlauthority.com/2011/02/06/sql-server-cxpacket-parallelism-usual-solution-wait-type-day-6-of-28/

  翻譯整理：Joe.TJ

  CXPACKET 已經成為所有等待類型中最常見的一種了。我通常會在多CPU系統的前五位等待類型統計中看見CXPACKET.

 

  聯機叢書：

    當嘗試同步查詢處理器交換迭代器時出現。如果針對該等待類型的爭用成為問題時，可以考慮降低並行度。

 CXPACKET 解釋：

    當為SQL查詢建立一個並行操作時，會有多個線程去執行這個查詢。每個查詢處理不同的資料集或行集。

      因為某些原因，一個或多個線程滯後，而產生了CXPACKET等待狀態。

      有一個組織/協調（organizer/coordinator）線程(Thread 0)，它需要等待所有線程完成並彙總資料來呈現給用戶端。

      組織線程必須等待所有線程完成處理才能進行下一步。由於組織線程等待緩慢的線程完成處理所產生的等待，就叫CXPACKET等待。

      請注意，並不是所有的CXPACKET等待類型都是不好的事情。你也許會遇某個CXPACKET等待是完全有意義的案例，有時它也是不可避免的。

      如果你在任何查詢上禁止此種等待，那麼查詢也許會變慢，因為不能為它執行並行操作。

 減少CXPACKET等待：

    我們不能拋開伺服器負載類型來討論減少CXPACKET等待。

   OLTP: 在純OLTP系統上，它的事務較短，查詢也不長，但是通常很快速。設定“Maximum degree of Parallelism”（MAXDOP）為1。

          這樣做可以確保查詢永遠不必使用並行方式運行，並且不會導致更多的資料庫引擎開銷。         

EXEC sys.sp_configure N‘cost threshold for parallelism‘, N‘1‘
GO
RECONFIGURE WITH OVERRIDE
GO

   Data-warehousing / Reporting server: 因為查詢執行時間一般較長，建議設定“Maximum degree of Parallelism”（MAXDOP）為0。

                                            這樣大多數查詢將會利用平行處理，執行時間較長的查詢也會受益於多處理器而提高效能。      

EXEC sys.sp_configure N‘cost threshold for parallelism‘, N‘0‘
GO
RECONFIGURE WITH OVERRIDE
GO

     Mixed System (OLTP & OLAP):這樣環境會是一個挑戰，必須找到正確的平衡點。我採取了非常簡單的方法。

                                 我設定“Maximum degree of Parallelism”（MAXDOP）為2，這樣意味著查詢仍會使用並行操作但是僅利用2顆CPU。

                                 然而，我把“並行查詢閥值”設定為較高的值，這樣的話，不是所有的查詢都有資格使用並行，除了那些查詢成本較高的查詢。

                                 在一個即有OLTP查詢又有報表伺服器的系統上，我發現這樣做運行得很好。

                                 在這裡我將會設定“‘Cost Threshold for Parallelism’”為25（）。你可以選擇任何值。但你只能通過在系統上做實驗來找到合適的值。

                                 在下面的指令碼中，我設定“Max Degree of Parallelism”為2，這樣的話，那些具有較高成本的查詢(這裡是25)，將會在2顆CPU上執行並行查詢。

                                 同時，不管伺服器有多少顆CPU，查詢只會選擇兩顆CPU來執行。               

EXEC sys.sp_configure N‘cost threshold for parallelism‘, N‘25‘
GO
EXEC sys.sp_configure N‘max degree of parallelism‘, N‘2‘
GO
RECONFIGURE WITH OVERRIDE
GO

 

--------------------------------------------

如蒙轉載或引用，請保留以下內容：
Joe‘s Blog:http://www.cnblogs.com/Joe-T/

 

完整維護過程：

 

這裡涉及兩個值:

cost threshold for parallelism 是預設設定 5S. the estimated cost 高於5S才安排並發

sp_configure ‘show advanced options‘, 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

sp_configure ‘max degree of parallelism‘, 4;--假如是8個(核)cpu

GO

RECONFIGURE WITH OVERRIDE;

GO

max degree of parallelism 能最大限制的控制並行導致CPU不可用而造成的短查詢的等待

sp_configure ‘show advanced options‘, 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

sp_configure ‘cost threshold for parallelism‘, 10;--將此時間增加

GO

RECONFIGURE WITH OVERRIDE;

GO

也可以單獨指定option(maxdop 1)來限制

SQL SERVER CXPACKET-Parallelism Wait Type 的慣用解決方案