今天我想談下一個特別的等待類型，這個等待可以說是有時很難進行故障排除：線程池（THREADPOOL）等待。這個等待類型是SQL Server內部線程調度機制特有的。

或許你已經知道SQL Server內部使用自有的“操作系統”來實現線程調度和內存管理——SQLOS。SQLOS提供一系列的工作者線程，用來執行提交給SQL Server的查詢。這問題就是這些工作者線程有時被耗盡——可能因為鎖/阻塞（Locking/Blocking）等情況。在這個情況下，SQL Server在引擎內部不能執行更多的請求，因為沒有空閑的工作線程可用。

你可以通過max worker threads選項（通過sp_configure）來配置SQLOS可用工作線程數。默認情況這個選項的值為0，這就是說SQL Server自身可以決定可以使用的工作者線程數。可用工作者線程數取決于CPU架構（x32，x64)和你有的可用CPU數。MSDN在線幫助提供下列不同組合情況的可用工作線程數：

CPU數　　　　32位　　　　64位

小于等于4個　　256　　　　512

8個　　　　　　288　　　　576

16個　　　　　 352　　　　704

32個　　　　　 480　　　　960

你也可以通過sys.dm_os_sys_info里的max_workers_count列來檢查你的SQL Server實例使用的工作線程數。

1 SELECT max_workers_count  FROM  sys.dm_os_sys_info

用下面的例子，我想演示SQL Server里如何獲得線程耗盡（thread starvation），還有你怎么解決它。

警告：請不要在生產系統里執行下列操作！！！

首先我們創建一個數據庫并建立一個簡單表作為我們樣例情景。我想盡可能的特例，因此表名和列名都是特別的。

 1 USE master 2 GO 3   4 CREATE DATABASE ThreadPoolWaits 5 GO 6   7 USE ThreadPoolWaits 8 GO 9  10 -- Create a new test table (this one will be unique on earth – hopefully…)11 CREATE TABLE [SomeCrazyUniqueTableName_6EFF088F-443B-4EBC-A4C7-9FC146D2EE49]12 (13     [MyUniqueColumnName1_F67DAC4A-C202-49BB-829A-071130BF1160] INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,14     [MyUniqueColumnName2_438B7184-B476-48A4-B5FA-DC34B99FA0A4] INT15 )16 GO17  18 -- Insert a record19 INSERT INTO [SomeCrazyUniqueTableName_6EFF088F-443B-4EBC-A4C7-9FC146D2EE49]20 VALUES (1)21 GO

從剛才的代碼里我們可以看到，我們的表定義非常簡單。下一步我會創建一個新的存儲過程，里面封裝了一些讀操作。

1 -- Create a stored procedure that encapsulates a read workload2 CREATE3 PROCEDURE MyCustomUniqueStoredProcedureName_ReadWorkload4 AS5 BEGIN6     SELECT * FROM [SomeCrazyUniqueTableName_6EFF088F-443B-4EBC-A4C7-9FC146D2EE49]7 END8 GO

最后我們開始一個新的事務，對剛才創建的表進行一個更新，但從不提交那個事務：

1 -- Begin a transaction that never commits…2 BEGIN TRANSACTION3     UPDATE [SomeCrazyUniqueTableName_6EFF088F-443B-4EBC-A4C7-9FC146D2EE49] WITH (TABLOCKX)4         SET [MyUniqueColumnName2_438B7184-B476-48A4-B5FA-DC34B99FA0A4] = 25 GO

至此，在你的SQL Server實例里有了個掛起的事務，因為TABLOCKX查詢提示，它有排它表鎖（exclusive table lock）。當你從不同的會話（新建查詢）執行剛才創建的存儲過程時，這個存儲過程會等待，因為它對讀取的記錄需要獲取共享鎖（Shared lock）。

1 EXEC MyCustomUniqueStoredProcedureName_ReadWorkload2 GO

你也可以通過DMV sys.dm_db_tran_locks來檢查這個鎖/阻塞（Locking/Blocking）情況，它會展示你一個等待請求：

1 SELECT2     resource_associated_entity_id,3     request_mode,4     request_status,5     request_session_id6 FROM sys.dm_tran_locks7 WHERE resource_database_id = DB_ID('ThreadPoolWaits') AND resource_type = 'OBJECT'8 GO

在這個簡單情況下，在SQL Server里只有一個等待查詢，沒啥特別發生。當你使用大量查詢，大于可用最大工作線程數時，SQL Server會作如何反應呢？我們來試下。我用ostress.exe來完成這個任務，它是微軟免費提供的RML工具的一部分。在我的配置（32位系統，4個CPU）SQL Server內部使用256個工作者線程。因為我通過下列的命令行，用ostress.exe模擬300個同時發生連接給SQL Server（RML Utilities for SQL Server->RML Cmd Prompt，請確認當前windows登錄帳號可以正常訪問當前新建的ThreadPoolWaits數據庫）：

ostress.exe -Q"EXEC ThreadPoolWaits.dbo.MyCustomUniqueStoredProcedureName_ReadWorkload" -n300

當你執行這個命令提示后，需要花費幾秒時間，ostress.exe才會創建300個工作者線程，并沒有什么特別發生，目前看起來都一切正常。

我們現在來分析下這個情況，通過SSMS新建一個查詢連接。我滴神呀，連接不上了：

SQL Server沒響應了！！！或者說已經掛掉！！！這就對了，因為現在我們已經耗盡了最大可用工作者線程。所有提交給SQL Server的請求當前都在等待一個共享鎖（LCK_M_S等待），而且所有其他的工作線程不能加入SQL Server內部隊列，因為已經沒有可用工作者線程了（THREADPOOL等待）。但是我們現在如何解決這個問題？重啟SQL Server一直不是個好選擇…….

幸運的是，自SQL Server 2005開始提供了數據庫管理員專用連接（Dedicated Admin Connection） （DAC）。你可以使用這個連接登錄到SQL Server，即使當工作者線程耗盡或內存壓力太大，因為在SQLOS內部DAC有它自己特有的

• 調度（Scheduler）
• 內存節點（Memory Node）
• TCP端口

因此SQL Server能接受并提供服務給DAC連接——即使像現在這個高度復雜的故障排除情況。但必須注意，對于整個SQL Server實例，只有1個可用的DAC連接。當你想通過DAC連接時，你必須使用下列語法：admin：<servername> ，這里<servername>就是你SQL Server實例名稱。因為讓我們啟動新的SSMS實例，通過DAC登錄到SQL Server。請注意，你不是通過DAC連接到對象瀏覽器（Object Explorer）。通過DAC連接只能使用簡單的查詢窗口：

當你通過DAC成功連接后，你就能運行你的診斷查詢。你必須知道：DAC不支持自動完成（智能提示），因為自動完成在背后使用它特有的連接——因此你要知道想用來故障排除的DMV。第1步我們可以檢查下sys.dm_exec_requests，看看在SQL Server里當前等待的請求：

 1 SELECT 2     r.command, 3     r.sql_handle, 4     r.plan_handle, 5     r.wait_type, 6     r.wait_resource, 7     r.wait_time, 8     r.session_id, 9     r.blocking_session_id10 FROM sys.dm_exec_requests r INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id11 WHERE s.is_user_process = 112 GO

在我的配置里，這個查詢返回我237行，這就是說237個用戶的請求（WHERE s.is_user_process = 1）在SQL Server里，當前在等待。但我們同時用300個連接的用戶啟動我們的存儲過程。sys.dm_exec_requests向你展示的只是那些在SQL Server內部潛在的工作者線程，因為這些請求當前正在SQL Server內部執行。但其他的呢？那些其他的掛起的請求通過sys.dm_os_waiting_tasks可以看到——它們都有THREADPOOL的等待：

1 SELECT * FROM sys.dm_os_waiting_tasks2 WHERE wait_type = 'THREADPOOL'3 GO

它們正在等待一個新的工作線程從工作池里釋放。但在我們的情況里，每個線程當前都暫停并束縛于用戶請求，因此這些請求會永遠等下去！你會看到THREADPOOL只在sys.dm_os_waiting_tasks里，從不在sys.dm_exec_requests里，因為在sys.dm_exec_requests里的請求已經束縛于SQL Server里的工作者線程。當你看sys.dm_exec_requests的輸出結果，在記錄集的最后你會看到session_id 列和blocking_session_id列。這2列向你展示了SQL Server內部的阻塞鏈（blocking chain）：

 1 SELECT 2     r.command, 3     r.sql_handle, 4     r.plan_handle, 5     r.wait_type, 6     r.wait_resource, 7     r.wait_time, 8     r.session_id, 9     r.blocking_session_id10 FROM sys.dm_exec_requests r INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id11 WHERE s.is_user_process = 1

可以看到，幾乎每個會話都有一個59的blocking_session_id，而且session_id59有一個53的blocking_session_id。session_id53就是我們阻塞頭（head blocker）！讓我們進一步分析下阻塞頭（head blocker）會話53：

 1 -- Analyze the head blocker session 2 SELECT 3     login_time, 4     [host_name], 5     [program_name], 6     login_name 7 FROM sys.dm_exec_sessions 8 WHERE session_id = 53 9 GO10  11 -- Analyze the head blocker connection12 SELECT13     connect_time,14     client_tcp_port,15     most_recent_sql_handle16 FROM sys.dm_exec_connections17 WHERE session_id = 5318 GO

這里最有意思的列，是來自sys.dm_exec_connections的most_recent_sql_handle列，這里我們可以獲得執行的SQL語句。

但你使用DMFsys.dm_exec_sql_text傳入most_recent_sql_handle列值時，你就可以拿到執行的SQL語句：

1 SELECT text FROM  sys.dm_exec_sql_text(0x01001400B883BC05A8831D06000000000000000000000000)2 GO

我們把語句復制出來看看：

BEGIN TRANSACTION UPDATE [SomeCrazyUniqueTableName_6EFF088F-443B-4EBC-A4C7-9FC146D2EE49] WITH (TABLOCKX) SET [MyUniqueColumnName2_438B7184-B476-48A4-B5FA-DC34B99FA0A4] = 2

這不就是我們最先的查詢么？我們把事務打開，但一直沒提交。現在我們跟蹤到了有問題的查詢，它導致了THREADPOOL和LCK_M_S等待，最后可以通過DAC的把這個會話殺掉：

1 KILL 532 GO

這個會話殺掉的話要花幾秒鐘，最后你的阻塞情況也消失了。現在你的SQL Server又能接受新的連接，可以正常工作了。當你通過DAC連接解決完問題后，不要忘記關閉這個連接，因為在SQL Server實例里只有1個可用DAC連接。然后當你查看SQL Server錯誤日志時，你可以看到類似下列的信息：（管理->SQL Server 日志->存檔編號1）

看不懂，沒關系，俺們再到Windows日志里看看：

這也象征著在你SQL Server實例內部工作者線程耗盡。這個情況的副作用也導致所謂的調度死鎖（Deadlocked Schedulers） 。當SQL Server遇到調度死鎖，SQL Server會在你的日志目錄寫入堆棧轉儲（Deadlo