SQL Server 在等待什麼資源?

SQL Server 在等待什麼?

ID-10032009

SQL Server 本身提供許多 DMV 內建系統 View

可以讓我們知道目前 SQL Server 在等候的資源為何?

該等候的狀態與資源可以讓我們更精準的判斷 SQL server 忙碌的原因

Select * from  [sys.dm_os_wait_stats] order by [wait_time_ms]

特別介紹幾個欄位

  • [wait_time_ms] =  CPU等待時間 + 其他資源(Disk/memory)等待時間
  • [signal_wait_time_ms] = CPU 等待時間

換句話說,

其他資源等待時間  = [wait_time_ms] – [signal_wait_time_ms]

 

SQL Server 定義的WaitType 有哪幾種呢? 這邊介紹幾種較為常見的

CXPACKET

CxPacket 表示因為多個CPU進行平行處理時,各個 Thread 同時執行時,

整個工作完成其實需要等待到需要時間最常的 thread 完成,才算整個工作完成。

舉例來說,下面有一個 Table 需要做讀取,有因此 thread 1~4分別讀取一部分,

Thread 0是負責管理 thread 1~4整體執行狀況。

Thread 1-3都很快就完成,其中 Thread 4 因為讀取所需的時間比較長,

因此,當 thread 1~3 工作完成時就會造成一些等待時間,必須等待到 Thread4完成為止,

對於Thread 1~3所發生的等待就是 CXPACKET

對於這種的 WaitType 通常是正常的,解決方法不完全需要把 Multiple-thread 執行取消

而是需要輔助看其他 WaitType 的情況,例如是否有 Disk I/O or Table Scan 等情況

CXPACKET1

 

CxPacket 與 PageIOLatch 一起發聲的話,表示有  Table Scan

造成Table Scan 的原因可能為:

  • 統計資訊的不正確
  • 沒有適當的 Index
  • 錯誤的 Query plan

 

SOS_SCHEDULER_YIELD

這種形式的 wait 主要為等待 CPU 執行,就是之前所說明的 Signal Wait

也間接說明有可能是CPU 的效能瓶頸

 

LCK_*

表示有很多 Lock 發生,Lock 會造成其他的 Query 被block 無法執行,

或是需要等待到取得該資源的 lock 之後才能繼續執行

因為可以進一步分析正在執行中的 SQL Query

 

PAGEIOLATCH_*, IO_COMPLETION, WRITELOG

這些都與 Disk I/O 有關

但是真正的原因有可能是因為 query 的設計不良所導致,例如,Table Scan等

 

PAGELATCH_*
這個主要指的是單純記憶體(buffer Cache)內部資源的等待。與 Disk I/O 完全無關。

最常見的一種例子,例如 TempDB 的存取

Query 時因為 join Table 所造成的 Hash Join or Merge Join 也會讓SQL Server 在 memory/TempDB作運算與處理。

(註:Loop Join 的運算僅發生於記憶體中)

 

LATCH_*

這是短期於記憶體物件間的資料存取的保護

與PageLatch 不同的是,這裡的記憶體指的是 internal cache

PageLatch 的記憶體是 Buffer Cache

可以配合  sys.dm_os_latch_stats 進一步查詢該 Latch 的原因與類型

 

ASYNC_NETWORK_IO

這個指的是 Network IO 瓶頸。

但是實務上,當這個 network IO 瓶頸發生的時候,

必須要查詢是否為 client 端應用程式的關係,

因此必須要讓 SQL Server 等待 client 的回覆,

所以看到這類的 Wait 時,可以檢查 client 應用程式端是否有問題。

 

OLEDB

使用 OLEDB 回傳資料就會出現這樣的 wait type

最常見的維 DBCC 指令

可能有背景程式執行 DBCC 的指令所造成。

 

總結

這篇文章介紹如何得知 SQL server 的 Wait

可以透過 [sys.dm_os_wait_stats]

還有幾種 SQL Server Wait 的類型與造成的原因

這些 Wait Type 就像健康檢查一樣,告訴我們的是一個結果的現象,

但是造成該問題的原因必須要關聯其他資訊來加以判斷

例如:CXPACKET 因為平行處理造成的 wait, 解決方式不是一昧的取消平行處理,而是要看是否有其他資源的 Wait 例如 Table Scan 所造成。

例如:ASYNC_NETWORK_IO 並不是表示網路 Switch/Router 有問題,而是要看為什麼 client端處理的時間比較長,造成SQL Server 端network 的等待。

善用這些 Wait 可以讓我們更進一步了解資源等待的狀況,

進一步做SQL Server 更好的效能調教。

Leave a Reply

Your email address will not be published.