线程池等待故障排除

今天我想谈下一个特别的等待类型，这个等待可以说是有时很难进行故障排除：线程池（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实例使用的工作线程数。

SELECT max_workers_count  FROM  sys.dm_os_sys_info

用下面的例子，我想演示SQL Server里如何获得线程耗尽（thread starvation），还有你怎么解决它。

警告：请不要在生产系统里执行下列操作！！！

首先我们创建一个数据库并建立一个简单表作为我们样例情景。我想尽可能的特例，因此表名和列名都是特别的。

USE master
GO
 
CREATE DATABASE ThreadPoolWaits
GO
 
USE ThreadPoolWaits
GO
 
-- Create a new test table (this one will be unique on earth – hopefully…)
CREATE TABLE [SomeCrazyUniqueTableName_6EFF088F-443B-4EBC-A4C7-9FC146D2EE49]
(
    [MyUniqueColumnName1_F67DAC4A-C202-49BB-829A-071130BF1160] INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    [MyUniqueColumnName2_438B7184-B476-48A4-B5FA-DC34B99FA0A4] INT
)
GO
 
-- Insert a record
INSERT INTO [SomeCrazyUniqueTableName_6EFF088F-443B-4EBC-A4C7-9FC146D2EE49]
VALUES (1)
GO

从刚才的代码里我们可以看到，我们的表定义非常简单。下一步我会创建一个新的存储过程，里面封装了一些读操作。

-- Create a stored procedure that encapsulates a read workload
CREATE
PROCEDURE MyCustomUniqueStoredProcedureName_ReadWorkload
AS
BEGIN
    SELECT * FROM [SomeCrazyUniqueTableName_6EFF088F-443B-4EBC-A4C7-9FC146D2EE49]
END
GO

最后我们开始一个新的事务，对刚才创建的表进行一个更新，但从不提交那个事务：

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

至此，在你的SQL Server实例里有了个挂起的事务，因为TABLOCKX查询提示，它有排它表锁（exclusive table lock）。当你从不同的会话（新建查询）执行刚才创建的存储过程时，这个存储过程会等待，因为它对读取的记录需要获取共享锁（Shared lock）。

EXEC MyCustomUniqueStoredProcedureName_ReadWorkload
GO

你也可以通过DMV sys.dm_db_tran_locks来检查这个锁/阻塞（Locking/Blocking）情况，它会展示你一个等待请求：

SELECT
    resource_associated_entity_id,
    request_mode,
    request_status,
    request_session_id
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('ThreadPoolWaits') AND resource_type = 'OBJECT'
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里当前等待的请求：

SELECT
    r.command,
    r.sql_handle,
    r.plan_handle,
    r.wait_type,
    r.wait_resource,
    r.wait_time,
    r.session_id,
    r.blocking_session_id
FROM sys.dm_exec_requests r INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE s.is_user_process = 1
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的等待：

SELECT * FROM sys.dm_os_waiting_tasks
WHERE wait_type = 'THREADPOOL'
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）：

SELECT
    r.command,
    r.sql_handle,
    r.plan_handle,
    r.wait_type,
    r.wait_resource,
    r.wait_time,
    r.session_id,
    r.blocking_session_id
FROM sys.dm_exec_requests r INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE s.is_user_process = 1

可以看到，几乎每个会话都有一个59的blocking_session_id，而且session_id59有一个53的blocking_session_id。session_id53就是我们阻塞头（head blocker）！让我们进一步分析下阻塞头（head blocker）会话53：

-- Analyze the head blocker session
SELECT
    login_time,
    [host_name],
    [program_name],
    login_name
FROM sys.dm_exec_sessions
WHERE session_id = 53
GO
 
-- Analyze the head blocker connection
SELECT
    connect_time,
    client_tcp_port,
    most_recent_sql_handle
FROM sys.dm_exec_connections
WHERE session_id = 53
GO

 这里最有意思的列，是来自sys.dm_exec_connections的most_recent_sql_handle列，这里我们可以获得执行的SQL语句。

但你使用DMF sys.dm_exec_sql_text传入most_recent_sql_handle列值时，你就可以拿到执行的SQL语句：

SELECT text FROM  sys.dm_exec_sql_text(0x01001400B883BC05A8831D06000000000000000000000000)
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的把这个会话杀掉：

KILL 53
GO

这个会话杀掉的话要花几秒钟，最后你的阻塞情况也消失了。现在你的SQL Server又能接受新的连接，可以正常工作了。当你通过DAC连接解决完问题后，不要忘记关闭这个连接，因为在SQL Server实例里只有1个可用DAC连接。然后当你查看SQL Server错误日志时，你可以看到类似下列的信息：
（管理->SQL Server 日志->存档编号1）

看不懂，没关系，俺们再到Windows日志里看看：

这也象征着在你SQL Server实例内部工作者线程耗尽。这个情况的副作用也导致所谓的调度死锁（Deadlocked Schedulers） 。当SQL Server遇到调度死锁，SQL Server会在你的日志目录写入堆栈转储（Deadlocked Schedulers）

有时我会看见在SQL Server内部盲目重新配置max worker threads设置，因为他们认为服务器的工作量需要更多的工作者线程。但是SQL Server里的每个问题，都有导致问题的根本原因，就如你刚才看到的一样。刚才情况的根源是一个未提交的事务，导致阻塞情况，引起线程耗尽，最后导致SQL Server无响应。从这个解释你可以发现，在你找到根源前都有很长的问题链——在你下次处理故障排除时，记住这个。

对你的操作，造成服务器几乎当机，别放在心上，因为俺们只是演习，不是在生产系统。重要的是我们学会了如何处理这个问题。请继续关注！

参考文章：

https://www.sqlpassion.at/archive/2011/10/25/troubleshooting-threadpool-waits/