今天我想谈下一个特别的等待类型，这个等待可以说是有时很难进行故障排除：线程池（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
这不就是我们最先的查询么？我们把事务打开，但一直没提交。现在我们跟踪到了有问题的查询，它导致了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
新闻热点
疑难解答
