CXPACKET這種等待類型(wait type)，是SQLSERVER最常見的等待類型之一。
CXPACKET這個詞來自於「Class Exchange Packet」，可以把它解釋為平行線程(thread)間的資料交換。
當SQLSERVER決定使用平行處理來執行一句SQL statement時，會啟動一個協調控制線程（coordinator/control
thread），線程編號為0，即thread 0，接著視所能使用的CPU核心數，分別啟動一個線程。
協調線程負責各線程間的協調，不參與SQL的執行。其餘的thread則按分配的工作同時執行。各個線程執行的時間有快有慢，先執行完的線程，需等待其它線程執行完成，這時的「等待」類型，即為CXPACKET。如下圖所示：
上圖各threads暗紅色部份的時間加總，即為執行此SQL statement的CXPACKET等待時間。
thread 0的CXPACKET的等待時間，其實就代表了整個SQL statement執行的時間。因此在平行處理的計畫中，CXPACKET是必然會發生的，即使各線程都同時完成。
通常的建議是「只要CXPACKET總時間不超過總等待時間的50%」，則不應把它視為一個問題而是一個指標。
我們可以使用下列語法，查詢各等待事件所佔的時間、佔比、累進佔比
WITH Waits AS
(
SELECT
wait_type,
wait_time_ms
/ 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type
NOT IN
('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',
'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',
'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT')
) -- filter out additional irrelevant waits
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2 ON W2.rn <= W1.rn
GROUP BY W1.rn,
W1.wait_type,
W1.wait_time_s,
W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold;
CXPACKET雖然是因平行處理而產生，但產生高的CXPACKET並不一定是「平行處理的問題」，常常它代表了其它問題的症狀。
例如CXPACKET如果伴隨著高的PAGEIOLATCH_*（可能還會見到IO_COMPLETION, ASYNC_IO_COMPLETION），這很大的背後原因是表示I/O子系統無法滿足平行處理的需求。這種情況下，你如果去調整最大並行度，或許會看到CXPACKET降低了，但其實你的系統效能（AP執行的反應時間）並不會得到提昇，問題沒有得到根本的解決。
這種情況，應首先考慮I/O的優化。
當然，直接去昇級I/O的硬體（例如換成SSD）也是一種方法，只是這有點強人所難了；我們可以針對相關的SQL語法/Table/index等進行優化（例如索引未匹配、碎片整理..等），以儘量減少I/O，然後再做觀察。
另外，是否會採用平行處理，以及平行處理的各個工作線程（如上圖的thread 1~4）的工作量分配，均是由系統依據統計值計算而來。如果統計值是正確的，那麼平行處理各工作線程的工作分配通常會較為平均，其CXPACKET的等待總時間應該不會太高。如果統計值過期，那就有可能不當的啟用平行處理，或者造成各線程工作分配不均，形成較大的CXPACKET等待。面對CXPACKET，也可以先嘗試由統計值更新著手。
當高CXPACKET伴隨著高SOS_SCHEDULER_YIELD及LACTH_*的時候，這種情況表示「平行處理」確實是問題了，這時候你可能需要去調整系統的平行處理組態。
SQL SERVER的平行處理程度，主要受下列兩個選項的控制
cost
threshold for parallelism （平行處理原則的成本臨界值）
SQL SERVER執行SQL前，先估計SQL所需的時間成本（單位為秒），如果大於上述臨界值（預設值為5），那麼就會採用平行處理來執行SQL語法。這個值該不該調整，該調成多少？讀者們可以參考下列兩篇文章（感謝PASS的好友Rock提供）：
max
degree of parallelism （平行處理原則的最大程度）
如果SQL SERVER決定採用平行處理，將依照max degree of parallelism選項所設定的值（threads）同時執行。0：代表無限制（最大限度）。
max degree of parallelism要設定為多少才是適當的，這個有許多種說法。主要還是要看實際的系統狀況而定。底下的表格僅為個人經驗的建議值。
上面的表格只是我個人的初始性建議，之後最好視系統實際的情況，觀察相關的效能參數，做出符合實際環境的調整。
沒有留言:
張貼留言