SQL調優日記:並行等待的原理和問題排查

發表於2016-10-28

概述

今天處理專案,客戶反應資料庫在某個時間段,反應特別慢。需要我們提供一些優化建議。

現象

由於是特定的時間段慢,排查起來就比較方便。直接檢視這個時間段資料庫的等待情況。檢視等待型別發現了大量的CXPAKET等待型別且等待時間長.

有的看官可能知道,出現這個等待類似時,可以適當降低最大並行度來解決。但是為什麼這麼做呢?降低並行度就一定可以解決問題嗎?

CXPAKET原理

那什麼是CXPAKET 等待呢。 當資料庫引擎分析查詢的開銷超過設定的閾值時,SQL SERVER會選擇並行執行。資料庫引擎會為這個請求建立多個任務。每個任務處理資料的一個子集。每個任務可以在一個分開的CPU/核上執行。請求主要使用生產-消費 佇列跟這些任務互動。如果這個佇列是空的,(即生產者沒有推入任何資料到這個佇列)。這個消費者必須暫停並且等待。相應等待型別就是CXPACKET 等待型別。顯示這個等待型別的請求 說明這個任務應該提供,但是沒有提供任何(或足夠)資料來消費。這些生產商任務反過來可能會暫停,等待一些其他型別的等待.

如下圖:索引掃描就是一個並行執行的動作。

打個比方

客戶端程式就是老闆,資料庫引擎是部門領導,老闆發出一個要求(request),檢視最近一年的銷售資料。領導一看這任務工作量大,一個人查太慢,要查到猴年馬月。果斷決定多派幾個人。一次最多可以派多少個攻城獅呢?(就取決於最大並行度)這裡假設是4個。這就分配4個人 小李、小王、小張、小陳去完成。 那這一年的任務怎麼分配呢? 以後再細說。 因為各種原因,其他人都做得了,小王還沒有完成。領導不可能拿著半成品的資料就去找老闆,只能等著小王。這就是CXPACKET.

排查

弄懂了CXPACKET的原理,那我們怎麼來排查這類問題呢?首先,小王並不是偷懶,他的工作能力和其他人是相同的。所以,我們需要找出小王慢的原因,

使用下面的指令碼:

通過上面的語句我們找到,並行等待正在等待LCK_M_S.說明查詢是被其他的操作阻塞了。上面的問題是由於一個寫入語句引起的。這個語句是一個很簡單的插入動作,為什麼寫入會這麼慢呢。可以檢視磁碟響應時間,,磁碟佇列

發現都出奇的高。

建議

看來問題是由於磁碟本身引起的。

1.更換讀寫速度更快的磁碟

2.目前資料檔案和日誌檔案在同一物理磁碟,分割開來

3.從業務出發。經過和客戶溝通後發現,這個表是操作日誌表。每次做業務操作都會記錄日誌。所以特別的大。

對應這樣的表,可以單獨建立資料夾組,檔案,並把表放在單獨的磁碟,緩解IO壓力

相關文章