Blocking讓Max Worker Threads爆了，造成SQL Server無法正常的提供服務

2016-03-02

5796
0
[SQL]TroubleShooting
2016-04-23

日前228連假第一天，帶著一家人去烤肉玩漆彈。卻在此時手機收到告警，系統偵測不到一台SQL Server。當下連接一台AP看看，果然發生網頁錯誤。Orz…..連假第一天就得救火，馬上殺回公司看看狀況。

當時狀況是OS依然健在的運作，CPU跟RAM及IO使用率都很低，但是SQL Server就是無法登入。第一時間研判是SQL的Threads爆了。本篇就來還原一下當時發生的情境。

由於我們安裝完SQL後不太會去改Server的Max Worker Threads，一般都採用預設值，下表則是預設的Max Worker Threads，根據不同的CPU數及SQL版本其預設值都不同。

CPU 數	32 位元電腦	64 位元電腦
<= 4 個處理器	256	512
8 個處理器	288	576
16 個處理器	352	704
32 個處理器	480	960
64 個處理器	736	1472
128 個處理器	4224	4480
256 個處理器	8320	8576

本LAB只是還原情境所以我先將Max Worker Threads調小一點，方便達到讓SQL Server爆Threads的狀況，如下圖所示，我將它調整成128。

壓測前我們先看一下一些基本數據，其實就是統計目前的Tasks及Threads及Workers數值為多少，如下圖所示。

我的LAB基是一部4核心的CPU，透過下圖語法查詢可以知道目前每一核心被分配多少Tasks有多少正在作業，有多少作業被Queue住在等待資源。注意下圖中work_queue_count壓測前都是0。

首先說明一下壓測的作法，我會先開啟一個交易去Update一張資料表，但不Commit。這時該資料表就有資料行處於鎖定狀態，此時我再開啟多條連線去Select該資料表。因此所有的連線都會因為該資料表有Lock而被Blocking住。一旦連線數夠多，那該SQL的Threads就會被用盡了。

下圖中我開啟一個交易並將tb_1資料表的所有資料的useriD改為1234且不Commit。

下圖語法會產生200個Connections，這200個Connections都只做一件事，就是去Select資料表tb_1。

用程式壓測後，我們試試看是否還可以登入該SQL Server。我的測試如下圖所示，會逾時然後產生錯誤，無法讓我們正常登入SQL。

即便改用sqlsmd也無法登入，如下圖所示。

發生這一種無法登入的狀況該怎麼辦啊。沒關係，此時可改用SQL的專用管理員連接(DAC)來登入資料庫。注意:開啟SSMS後先按取消不要做登入。然後再SSMS介面中點選如下圖紅色圈選處[Database Engine查詢] 來登入。

按下 [Database Engine查詢] 圖示後會跳出如下圖中的畫面，注意下圖紅色圈選處顯示字串是寫連接到Database Engine。如果是顯示連接到Server那是錯的歐。

注意下圖藍色圈選處，DAC連線要在伺服器名稱前加入admin:字串歐，沒有加該字串那就是還採一般模式登入。

用DAC方式登入SQL後，我們一樣檢視一下相關統計值，Tasks及Threads和Workers都大幅提升

觀察一下schedulers，可以看見4個核心都排滿了工作，而work_queue_count也不是0了。這表示了目前SQL的Threads已達最大值，目前已無Threads可以分配給Tasks，因此work_queue_count會大於0。

如下圖所示，我們可以發現被Blocking住的Processes有136個。

透過下面語法可以找出Blocking的源頭

WITH RootBlocking AS
 (
     SELECT DISTINCT blocking_session_id FROM sys.dm_exec_requests 
     WHERE blocking_session_id > 50 
     AND blocking_session_id  not In 
         ( SELECT session_id FROM sys.dm_exec_requests WHERE blocking_session_id > 50 )
 )
 SELECT ses.session_id,ses.host_name,ses.program_name,ses.login_name,ses.status, ses.last_request_end_time,
     ct1.text sql_text,ct2.text recent_sql_text
 FROM RootBlocking rot
 INNER JOIN sys.dm_exec_connections con ON rot.blocking_session_id = con.session_id
 INNER JOIN sys.dm_exec_sessions ses ON rot.blocking_session_id = ses.session_id
 LEFT  JOIN sys.dm_exec_requests req ON rot.blocking_session_id = req.session_id
 OUTER APPLY sys.dm_exec_sql_text(req.sql_handle) ct1
 OUTER APPLY sys.dm_exec_sql_text(con.most_recent_sql_handle) ct2

下圖中我們看見造成Blocking的源頭是SessionID 53