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

日前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

 

Kill該Session,如下圖。

 

當我們將造成Blocking的Session砍掉後,我們再看一下schedulers的數值。剛剛累積的工作都完成了,work_queue_count又降為0。

 

當時公司的SQL狀況就是如同我模擬的,一個Update的交易一直沒有Commit。而後續的Select都被卡住直到SQL的Threads被用盡,無法正常的提供服務。所以當時的Server資源都處於很低的使用率,但卻無法正常提供服務。

補上一張圖,說明Connection / Bath / Task / Worker / Schedule關聯

參考資料來源 :

sys.dm_os_schedulers
利用 SQL Server Management Studio 使用專用管理員連接
設定 max worker threads 伺服器組態選項

我是ROCK

rockchang@mails.fju.edu.tw