[翻译]SQL Server等待事件—THREADPOOL

前言：本文是对博客threadpool的翻译,本文也不是完全直接翻译，有些地方适当加入了自己的一些理解和认知。如有翻译不对或不好的地方，敬请指出，大家一起学习进步。尊重原创和翻译劳动成果，转载时请注明出处。谢谢！

英文原文地址：THREADPOOL^[1]

事件描述：

这个等待类型出现是因为服务器的线程池没有可用的线程，它可能导致登录失败或查询语句无法正常运行。

（官方联机丛书描述：“当任务运行时在等待工作线程时出现这个等待事件。这可能表明参数max worker threads的值设置过低, 或者批处理执行过长, 从而减少了可用于满足其他批处理的工作线程（worker thread）数量。

Questions/comments on this wait type? Click here to send Paul an email, especially if you have any information to add to this topic.

Added in SQL Server version:
Pre-2005/2005

Removed in SQL Server version:
N/A

扩展事件wait_type的值:

这个等待类型在sys.dm_xe_map_vlaues中对应的扩展事件为SOS_WORKER  (感谢乔纳森的帖子^[2])。

sys.dm_xe_map_values中的map_key值在SQL Server 2008和 SQL Server 2008 R2 中为113, 在SQL Server 2012和 2014 RTM中值为117。在 SQL Server 2014 RTM 之后, 您必须检查DMV视图获得它的最新的值, 因为一些map_keyvalues 的值在以后的SQL Server版本中改变了。

其它信息

SQL Server实例在启动的时候创建了一定数量的工作线程（worker threads），包括一些用于后台任务和少量线程用于填充线程池。线程池的线程数量将根据需要动态的增长和收缩，直至实例的最大工作线程数（max worker threads）。

举个例子， 我的笔记本的CPU有8个逻辑处理器，因此SQL Server实例启动的时候创建了576个工作线程。您可以使用select count (*) from sys.dm_os_workers 这个SQL脚本查看实例当前存在的线程数。你也可以从sys.dm_os_sys_info视图中的max_worker_count列，查看你的实例的最大工作线程数量。关于SQL Server会创建多少个工作线程的详细信息,你可以参考联机丛书^[3]

当一个查询语句将要执行时，SQL Server会决定它需要多少个线程（详细信息请见Paul Whtile的博客），并且决定在thread pool保留那么多线程，或者创建所需数量的线程。当然前提是没有超过最大线程数（max worker threads）。如果线程池当前没有足够可用的线程，此时"THREADPOOL等待"就会出现，如果线程池没有可用的线程， 连接到SQL Server就会失败。

这意味着SQL Server实例突然需要的线程数超过线程池中可用的线程数量，您会看到一些短时间的 THREADPOOL 等待（THREADPOOL waits），这是正常的。当根本没有可用线程时，这是工作线程饥饿，此时就是不正常现象。

工作线程发生线程饥饿（Worker thread starvation）的原因有很多，包括下面一些情况：

• 一个线程获取了一个锁，然后阻塞了所有其它线程，越来越多的连接出现并被阻塞，最终耗尽了线程池（thread pool）资源。

  这种情况可以从sys.dm_os_waiting_tasks 这个DMV视图中（使用我下面的脚本）找出被单个SPID阻塞的信息，并考虑将其杀死

/*============================================================================
  File:     WaitingTasks.sql
 
  Summary:  Snapshot of waiting tasks
 
  SQL Server Versions: 2005 onwards
------------------------------------------------------------------------------
  Written by Paul S. Randal, SQLskills.com
 
  (c) 2016, SQLskills.com. All rights reserved.
 
  For more scripts and sample code, check out 
    http://www.SQLskills.com
 
  You may alter this code for your own *non-commercial* purposes. You may
  republish altered code as long as you include this copyright and give due
  credit, but you must obtain prior permission before blogging this code.
   
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/
SELECT
    [owt].[session_id] AS [SPID],
    [owt].[exec_context_id] AS [Thread],
    [ot].[scheduler_id] AS [Scheduler],
    [owt].[wait_duration_ms] AS [wait_ms],
    [owt].[wait_type],
    [owt].[blocking_session_id] AS [Blocking SPID],
    [owt].[resource_description],
    CASE [owt].[wait_type]
        WHEN N'CXPACKET' THEN
            RIGHT ([owt].[resource_description],
                CHARINDEX (N'=', REVERSE ([owt].[resource_description])) - 1)
        ELSE NULL
    END AS [Node ID],
    [eqmg].[dop] AS [DOP],
    [er].[database_id] AS [DBID],
    CAST ('https://www.sqlskills.com/help/waits/' + [owt].[wait_type] as XML) AS [Help/Info URL],
    [eqp].[query_plan],
    [est].text
FROM sys.dm_os_waiting_tasks [owt]
INNER JOIN sys.dm_os_tasks [ot] ON
    [owt].[waiting_task_address] = [ot].[task_address]
INNER JOIN sys.dm_exec_sessions [es] ON
    [owt].[session_id] = [es].[session_id]
INNER JOIN sys.dm_exec_requests [er] ON
    [es].[session_id] = [er].[session_id]
FULL JOIN sys.dm_exec_query_memory_grants [eqmg] ON
    [owt].[session_id] = [eqmg].[session_id]
OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]
OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp]
WHERE
    [es].[is_user_process] = 1
ORDER BY
    [owt].[session_id],
    [owt].[exec_context_id];
GO

• 数百个连接正在执行并行查询计划，耗尽了线程池。

  请参阅CXPACKET等待如何识别并行执行计划，尽可能减少并行的总量发生。

• 许多连接正在执行一个查询计划，并且查询时间比平时要长，耗尽了线程池

  请参阅CXPACKET等待如何识别偏斜并行性(skewed parallelism)。

  还要查找那些长时间运行的查询语句，并调查其正在发生的等待，以查看是否存在常规性能问题导致线程匮乏，或者那些长时间运行的SQL语句是否有不正确的查询计划。

• SQL Server中的活动会话数等于工作线程数

检查sys.dm_exec_requests视图中的记录数，如果记录数接近工作线程数量， 减少连接数量（例如，应用程序是否没有使用连接池或没有正确关闭）或增加max worker threads的值。请注意，由于空闲连接不消耗工作线程，因此与SQL Server连接的数量可能超过活动（Active）的连接，这可能是完全正常的。

• 对max worker thread参数的不正确配置。

查看max worker worker thread 选项的值并设置为自动调整。请参阅此联机丛书^[4]

• 内存设置配置错误，导致Windows所需内存不足

如果由于工作线程饥饿（worker thread starvation）导致无法连接到SQL Server去进行故障诊断，请尝试使用专用管理员连接（DAC）。

Known occurrences in SQL Server (list number matches call stack list):
1. Waiting for a worker thread to become available
Abbreviated call stacks (list number matches known occurrences list):
1. SOS_Scheduler::UpdateWaitTimeStats+30c
WorkDispatcher::DequeueTask+211
SOS_Scheduler::ProcessTasks+1e3
SchedulerManager::WorkerEntryPoint+261
SystemThread::RunWorker+8f
SystemThreadDispatcher::ProcessWorker+3c8
SchedulerManager::ThreadEntryPoint+236
BaseThreadInitThunk+d
RtlUserThreadStart+1d

参考资料