合 MSSQL的服务器配置选项中的最大工作线程数（max worker threads）

简介

本文说明如何使用 SQL Server Management Studio 或 Transact-SQL 在 SQL Server 中配置“最大工作线程数”服务器配置选项。 “最大工作线程数”选项配置 SQL Server 范围内可用于处理查询请求、登录、注销和类似应用程序请求的工作线程数。

SQL Server 使用操作系统的本机线程服务来确保满足以下条件：

• 一个或多个线程同时支持 SQL Server 支持的每个网络。
• 一个线程处理数据库检查点。
• 一个线程池处理所有用户。

max worker threads 的默认值为 0。 这使 SQL Server 在启动时自动配置工作线程数。 默认设置对于大多数系统为最佳设置。 不过，根据您的系统配置，有时将 max worker threads 设置为特定值会提高性能。

限制和局限

• 如果实际的查询请求数超过了“最大工作线程数”中设置的值，SQL Server 就会将工作线程集中到池中，这样下一个可用的工作线程就可以处理请求了。 仅将工作线程分配给活动请求，并在处理请求后释放该工作线程。 即使发出请求的用户会话/连接保持打开状态，也会发生这种情况。

• “最大工作线程数”服务器配置选项不限制引擎中可能生成的所有线程。 LazyWriter、Checkpoint、日志编写器、Service Broker、锁管理器或其他任务所需的系统线程在此限制之外生成。 可用性组使用“最大工作线程数限制”范围内的一些工作线程，但也使用系统线程（请参阅可用性组的线程使用情况）。如果超过了配置的线程数，下列查询提供有关已生成附加线程的系统任务的信息。

  SQL复制

  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;

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

  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 工作器数)。

  逻辑 CPU 数	32 位计算机（不高于 SQL Server 2014 (12.x)）	64 位计算机（不高于 SQL Server 2016 (13.x) SP1）	64 位计算机（自 SQL Server 2016 (13.x) SP2 和 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

  在不高于使用 Service Pack 1 的 SQL Server 2016 (13.x) 的版本中，“每 CPU 工作器数”只取决于体系结构（32 位还是 64 位）：

  逻辑 CPU 数	32 位计算机1	64 位计算机
  <= 4	256	512
  > 4	256 + ((逻辑 CPU 数 - 4) * 8)	512 2 + ((逻辑 CPU 数 - 4) * 16)

  自 SQL Server 2016 (13.x) SP2 和 SQL Server 2017 (14.x) 起，“每 CPU 工作器数”取决于体系结构和处理器数（介于 4 和 64 之间还是大于 64）：

  逻辑 CPU 数	32 位计算机1	64 位计算机
  <= 4	256	512
  > 4 和 <= 64	256 + ((逻辑 CPU 数 - 4) * 8)	512 2 + ((逻辑 CPU 数 - 4) * 16)
  > 64	256 + ((逻辑 CPU 数 - 4) * 32)	512 2 + ((逻辑 CPU 数 - 4) * 32)

  1 自 SQL Server 2016 (13.x) 起，SQL Server 不能再安装在 32 位操作系统上。 为了帮助运行 SQL Server 2014 (12.x) 及更低版本的客户，我们列出了 32 位计算机值。 建议对 32 位计算机上运行的 SQL Server 实例使用最大工作线程数 1,024。

  本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
  后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

  验证码：

  获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

  相关文章

  • sqlcmd备份MSSQL数据库报错 SSL Provider: [error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed:self signed certificate]
  • MSSQL中的幽灵数据删除进程、虚影清除进程（ghost cleanup）
  • SSMS配置错误，导致 SELECT 失败，因为下列 SET 选项的设置不正确: 'QUOTED_IDENTIFIER, ANSI_PADDING'。请确保 SET 选项正确无误，可以用于 计算列上的索引视图和/或索引和/或筛选的索引和/或查询通知和/或 XML 数据类型方法和/或空间索引操作。。
  • MSSQL删除tempdb的数据文件
  • MSSQL中的等待事件SQLTRACE_FILE_BUFFER说明
  • MSSQL的数据库快照
  • MSSQL 2014新功能之延迟事务持续性(Delayed Transaction Durability)--延迟提交
  • MSSQL中的实时查询统计
  • MSSQL中查询索引的创建时间和删除时间
  • MSSQL中查询依赖关系
  • MSSQL的ldf日志文件丢失, 如何仅用mdf文件恢复数据库呢
  • MSSQL移动数据库文件（分离和附加）
  • Windows的资源监视器中进程对磁盘的响应时间多少毫秒即为延迟比较厉害影响磁盘性能IO
  • MSSQL扩展事件之system_health会话
  • MSSQL的创建索引占用的内存
  • MSSQL 2014新功能之缓存池扩展BPE (Buffer Pool Extension)
  • MSSQL中的锁和阻塞
  • MSSQL中的数据库兼容性级别
  • MSSQL中的索引填充因子（fill factor）
  • MSSQL服务主密钥
  打赏赞(1)分享