今天我想談下一個特別的等待類型，這個等待可以說是有時很難進行故障排除：線程池（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有它自己特有的
因此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
這不就是我們最先的查詢么？我們把事務打開，但一直沒提交?，F在我們跟蹤到了有問題的查詢，它導致了THREADPOOL和LCK_M_S等待，最后可以通過DAC的把這個會話殺掉：
1 KILL 532 GO
這個會話殺掉的話要花幾秒鐘，最后你的阻塞情況也消失了?，F在你的SQL Server又能接受新的連接，可以正常工作了。當你通過DAC連接解決完問題后，不要忘記關閉這個連接，因為在SQL Server實例里只有1個可用DAC連接。然后當你查看SQL Server錯誤日志時，你可以看到類似下列的信息：（管理->SQL Server 日志->存檔編號1）
看不懂，沒關系，俺們再到Windows日志里看看：
這也象征著在你SQL Server實例內部工作者線程耗盡。這個情況的副作用也導致所謂的調度死鎖（Deadlocked Schedulers） 。當SQL Server遇到調度死鎖，SQL Server會在你的日志目錄寫入堆棧轉儲（Deadlo
新聞熱點
疑難解答
