【SQLServer】max worker threads参数说明

2022-09-11 10:22 abce 阅读(895) 评论(0) 编辑收藏举报

本文介绍如何使用SQL Server Management Studio或Transact-SQL在SQL Server中配置最大工作线程服务器配置选项。

max worker threads选项配置SQL Server可用于处理查询请求、登录、注销和类似应用程序请求的工作线程数。

SQL Server使用操作系统的本地线程服务来确保以下条件：
·一个或多个线程同时支持SQL Server支持的每个网络。
·一个线程处理数据库检查点。
·一个线程池处理所有用户。

max worker threads的默认值为0。这使SQL Server能够在启动时自动配置工作线程数。默认设置适合大多数系统。但是，根据你的系统配置，将最大工作线程数设置为特定值有时会提高性能。

查询请求的实际数量可能会超过max worker threads中设置的值，在这种情况下，SQL Server将工作线程池化，以便下一个可用的工作线程可以处理请求。工作线程仅分配给活动请求，并在请求得到服务后释放。即使发出请求的用户会话/连接保持打开，也会发生这种情况。

max worker threads配置选项不会限制引擎内可能产生的所有线程。LazyWriter、Checkpoint、Log Writer、Service Broker、Lock Manager或其他任务所需的系统线程在此限制之外生成。可用性组使用max worker threads限制内的一些工作线程，但如果超过配置的线程数，也使用系统线程，以下查询将提供有关系统任务产生的额外线程信息。

SELECT 
s.session_id,  
r.command,  
r.status,  
r.wait_type,  
r.scheduler_id,  
w.worker_address,  
w.is_preemptive,  
w.state,  
t.task_state,  
t.session_id,  
t.exec_context_id,  
t.request_id  
FROM sys.dm_exec_sessions AS s  
INNER JOIN sys.dm_exec_requests AS r  
    ON s.session_id = r.session_id  
INNER JOIN sys.dm_os_tasks AS t  
    ON r.task_address = t.task_address  
INNER JOIN sys.dm_os_workers AS w  
    ON t.worker_address = w.worker_address  
WHERE s.is_user_process = 0;

以下是设置建议：

此选项是一个高级选项，只能由经验丰富的数据库管理员或经过认证的SQL Server专业人员进行更改。如果你怀疑存在性能问题，有可能不是工作线程的可用性原因。原因更可能与占用工作线程而不释放它们的活动有关。比如长时间运行的查询或瓶颈（I/O、阻塞、闩锁等待、网络等待）。最好在更改最大工作线程设置之前找到性能问题的根本原因。

当大量客户端连接到服务器时，线程池有助于优化性能。通常，会为每个查询请求创建一个单独的操作系统线程。但是，对于数百个与服务器的连接，每个查询请求使用一个线程会消耗大量系统资源。max worker threads选项使SQL Server能够创建一个工作线程池来服务更多的查询请求，从而提高性能。

下表显示了根据CPU、计算机体系结构和SQL Server版本的各种组合自动配置的最大工作线程数（当值设置为0时），使用公式：默认最大工作线程数+((逻辑CPU-4)*每个CPU的worker线程数)

Number of logical CPUs	32-bit computer (up to SQL Server 2014 (12.x))	64-bit computer (up to SQL Server 2016 (13.x) SP1)	64-bit computer (starting with SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x))
<= 4	256	512	512
8	288	576	576
16	352	704	704
32	480	960	960
64	736	1472	1472
128	1248	2496	4480
256	2272	4544	8576

截至SQL Server 2016 (13.x) SP1,每个CPU的worker线程数取决于cpu的架构(32位还是64位)：

Number of logical CPUs	32-bit computer Note 1	64-bit computer
<= 4	256	512
> 4	256 + ((logical CPU's - 4) * 8)	512 Note 2 + ((logical CPU's - 4) * 16)

从SQL Server 2016 (13.x) SP2和SQL Server 2017 (14.x)起，每个CPU的worker线程数取决于cpu的架构(32位还是64位)和处理器的个数：

Number of logical CPUs	32-bit computer Note 1	64-bit computer
<= 4	256	512
> 4 and <= 64	256 + ((logical CPU's - 4) * 8)	512 Note 2 + ((logical CPU's - 4) * 16)
> 64	256 + ((logical CPU's - 4) * 32)	512 Note 2 + ((logical CPU's - 4) * 32)

当所有工作线程都在长时间运行的查询中处于活动状态时，SQL Server可能会显示为无响应，直到工作线程完成并变得可用。虽然这不是缺陷，但有时可能是不可取的。如果某个进程似乎没有响应并且无法处理新查询，则使用专用管理员连接(DAC)连接到SQL Server，并终止该进程。为防止这种情况，请增加最大工作线程数。