概述
今天處理專案,客戶反應資料庫在某個時間段,反應特別慢。需要我們提供一些優化建議。
現象
由於是特定的時間段慢,排查起來就比較方便。直接檢視這個時間段資料庫的等待情況。檢視等待型別發現了大量的CXPAKET等待型別且等待時間長.
有的看官可能知道,出現這個等待類似時,可以適當降低最大並行度來解決。但是為什麼這麼做呢?降低並行度就一定可以解決問題嗎?
CXPAKET原理
那什麼是CXPAKET 等待呢。 當資料庫引擎分析查詢的開銷超過設定的閾值時,SQL SERVER會選擇並行執行。資料庫引擎會為這個請求建立多個任務。每個任務處理資料的一個子集。每個任務可以在一個分開的CPU/核上執行。請求主要使用生產-消費 佇列跟這些任務互動。如果這個佇列是空的,(即生產者沒有推入任何資料到這個佇列)。這個消費者必須暫停並且等待。相應等待型別就是CXPACKET 等待型別。顯示這個等待型別的請求 說明這個任務應該提供,但是沒有提供任何(或足夠)資料來消費。這些生產商任務反過來可能會暫停,等待一些其他型別的等待.
如下圖:索引掃描就是一個並行執行的動作。
打個比方
客戶端程式就是老闆,資料庫引擎是部門領導,老闆發出一個要求(request),檢視最近一年的銷售資料。領導一看這任務工作量大,一個人查太慢,要查到猴年馬月。果斷決定多派幾個人。一次最多可以派多少個攻城獅呢?(就取決於最大並行度)這裡假設是4個。這就分配4個人 小李、小王、小張、小陳去完成。 那這一年的任務怎麼分配呢? 以後再細說。 因為各種原因,其他人都做得了,小王還沒有完成。領導不可能拿著半成品的資料就去找老闆,只能等著小王。這就是CXPACKET.
排查
弄懂了CXPACKET的原理,那我們怎麼來排查這類問題呢?首先,小王並不是偷懶,他的工作能力和其他人是相同的。所以,我們需要找出小王慢的原因,
使用下面的指令碼:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
select r.session_id, status, command, r.blocking_session_id, r.wait_type as[request_wait_type], r.wait_time as[request_wait_time], t.wait_type as[task_wait_type], t.wait_duration_ms as[task_wait_time], t.blocking_session_id, t.resource_description from sys.dm_exec_requests r LEFT join sys.dm_os_waiting_tasks t on r.session_id = t.session_id where r.session_id >=50 and r.session_id <> spid; |
通過上面的語句我們找到,並行等待正在等待LCK_M_S.說明查詢是被其他的操作阻塞了。上面的問題是由於一個寫入語句引起的。這個語句是一個很簡單的插入動作,為什麼寫入會這麼慢呢。可以檢視磁碟響應時間,,磁碟佇列
發現都出奇的高。
建議
看來問題是由於磁碟本身引起的。
1.更換讀寫速度更快的磁碟
2.目前資料檔案和日誌檔案在同一物理磁碟,分割開來
3.從業務出發。經過和客戶溝通後發現,這個表是操作日誌表。每次做業務操作都會記錄日誌。所以特別的大。
對應這樣的表,可以單獨建立資料夾組,檔案,並把表放在單獨的磁碟,緩解IO壓力