配置 max worker threads 服务器配置选项Configure the max worker threads Server Configuration Option

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions)

本主题说明如何使用 或 SQL ServerSQL ServerSQL Server Management StudioSQL Server Management Studio 中配置 max worker threads Transact-SQLTransact-SQL服务器配置选项。This topic describes how to configure the max worker threads server configuration option in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. “最大工作线程数”选项配置 SQL ServerSQL Server 范围内可用于处理查询请求、登录、注销和类似应用程序请求的工作线程数。The max worker threads option configures the number of worker threads that are available SQL ServerSQL Server-wide to process query requests, login, logout, and similar application requests.

SQL ServerSQL Server 使用操作系统的本机线程服务来确保满足以下条件:uses the native thread services of the operating systems to ensure the following conditions:

  • 一个或多个线程同时支持 SQL ServerSQL Server 支持的每个网络。One or more threads simultaneously support each network that SQL ServerSQL Server supports.

  • 一个线程处理数据库检查点。One thread handles database checkpoints.

  • 一个线程池处理所有用户。A pool of threads handles all users.

max worker threads 的默认值为 0。The default value for max worker threads is 0. 这使 SQL ServerSQL Server 在启动时自动配置工作线程数。This enables SQL ServerSQL Server to automatically configure the number of worker threads at startup. 默认设置对于大多数系统为最佳设置。The default setting is best for most systems. 不过,根据您的系统配置,有时将 max worker threads 设置为特定值会提高性能。However, depending on your system configuration, setting max worker threads to a specific value sometimes improves performance.

开始之前Before You Begin

限制和局限Limitations and Restrictions

  • 如果实际的查询请求数超过了“最大工作线程数”中设置的值,SQL ServerSQL Server 就会将工作线程集中到池中,这样下一个可用的工作线程就可以处理请求了。The actual number of query requests can exceed the value set in max worker threads in which case SQL ServerSQL Server pools the worker threads so that the next available worker thread can handle the request. 仅将工作线程分配给活动请求,并在处理请求后释放该工作线程。A worker thread is assigned only to active requests and is released once the request is serviced. 即使发出请求的用户会话/连接保持打开状态,也会发生这种情况。This happens even if the user session/connection on which the request was made remains open.

  • “最大工作线程数”服务器配置选项不限制引擎中可能生成的所有线程。The max worker threads server configuration option does not limit all threads that may be spawned inside the engine. LazyWriter、Checkpoint、日志编写器、Service Broker、锁管理器或其他任务所需的系统线程在此限制之外生成。System threads required for tasks such as LazyWriter, Checkpoint, Log Writer, Service Broker, Lock Manager, or others are spawned outside this limit. 可用性组使用“最大工作线程数限制”范围内的一些工作线程,但也使用系统线程(请参阅可用性组的线程使用情况)。如果超过了配置的线程数,下列查询提供有关已生成附加线程的系统任务的信息。Availability Groups use some of the worker threads from within the max worker thread limit but also use system threads (see Thread Usage by Availability Groups ) If the number of threads configured is being exceeded, the following query will provide information about the system tasks that have spawned the additional 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;  

建议Recommendations

  • 此选项是一个高级选项,仅应由有经验的数据库管理员或认证的 SQL ServerSQL Server 专业人员更改。This option is an advanced option and should be changed only by an experienced database administrator or certified SQL ServerSQL Server professional. 如果怀疑存在性能问题,可能不是由于工作线程不可用。If you suspect that there is a performance problem, it is probably not the availability of worker threads. 原因很可能与占用工作线程且未释放它们的活动有关。The cause is more likely related to activies that occupy the worker threads and do not release them. 例如长时间运行的查询或导致长时间等待的查询的系统瓶颈(I/O、阻塞、闩锁等待、网络等待)。Examples include long-running queries or bottlenecks on the system (I/O, blocking, latch waits, network waits) that cause long-waiting queries. 在更改最大工作线程设置之前,最好找到导致性能问题的根本原因。It is best to find the root cause of a performance issue before you change the max worker threads setting. 有关评估性能的详细信息,请参阅监视和优化性能For more information on assessing performance, see Monitor and tune for performance.

  • 当服务器上连接有大量客户端时,线程池有助于优化性能。Thread pooling helps optimize performance when a large number of clients connect to the server. 一般情况下,会为每个查询请求创建一个单独的操作系统线程。Usually, a separate operating system thread is created for each query request. 但是,当到服务器的连接达到数以百计时,为每个查询请求使用一个线程会占用大量的系统资源。However, with hundreds of connections to the server, using one thread per query request can consume large amounts of system resources. max worker threads 选项使 SQL ServerSQL Server 可以为更大数量的查询请求创建一个工作线程池,这将提高性能。The max worker threads option enables SQL ServerSQL Server to create a pool of worker threads to service a larger number of query requests, which improves performance.

  • 下表显示了根据 CPU、计算机体系结构和 SQL ServerSQL Server 版本的各种组合自动配置的最大工作线程数(当值设置为 0 时),计算公式如下:默认最大工作器数 + ((逻辑 CPU 数* - 4) * 每 CPU 工作器数)** 。The following table shows the automatically configured number of max worker threads (when value is set to 0) based on various combinations of CPUs, computer architecture, and versions of SQL ServerSQL Server, using the formula: Default Max Workers + (( logical CPUs - 4) * Workers per CPU)**.

    CPU 数Number of CPUs 32 位计算机(不高于 SQL Server 2014 (12.x)SQL Server 2014 (12.x)32-bit computer (up to SQL Server 2014 (12.x)SQL Server 2014 (12.x)) 64 位计算机(不高于 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1)64-bit computer (up to SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1) 64 位计算机(自 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 起)64-bit computer (starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x))
    <= 4<= 4 256256 512512 512512
    88 288288 576576 576576
    1616 352352 704704 704704
    3232 480480 960960 960960
    6464 736736 14721472 14721472
    128128 12481248 24962496 44804480
    256256 22722272 45444544 85768576

    在不高于 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 的版本中,“每 CPU 工作器数”只取决于体系结构(32 位还是 64 位):Up to SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, the Workers per CPU only depend on the architecture (32-bit or 64-bit):

    CPU 数Number of CPUs 32 位计算机132-bit computer 1 64 位计算机64-bit computer
    <= 4<= 4 256256 512512
    > 4> 4 256 +((逻辑 CPU 位数 - 4)* 8)256 + ((logical CPU's - 4) * 8) 5122 + ((逻辑 CPU 数 - 4) * 16)512 2 + ((logical CPU's - 4) * 16)

    SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 起,“每 CPU 工作器数”取决于体系结构和处理器数(介于 4 和 64 之间还是大于 64):Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x), the Workers per CPU depend on the architecture and number of processors (between 4 and 64, or greater than 64):

    CPU 数Number of CPUs 32 位计算机132-bit computer 1 64 位计算机64-bit computer
    <= 4<= 4 256256 512512
    > 4 和 <= 64> 4 and <= 64 256 +((逻辑 CPU 位数 - 4)* 8)256 + ((logical CPU's - 4) * 8) 5122 + ((逻辑 CPU 数 - 4) * 16)512 2 + ((logical CPU's - 4) * 16)
    > 64> 64 256 + ((逻辑 CPU 位数 - 4) * 32)256 + ((logical CPU's - 4) * 32) 5122 + ((逻辑 CPU 数 - 4) * 32)512 2 + ((logical CPU's - 4) * 32)

    1SQL Server 2016 (13.x)SQL Server 2016 (13.x) 起,SQL ServerSQL Server 不能再安装在 32 位操作系统上。1 Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), SQL ServerSQL Server can no longer be installed on a 32-bit operating system. 为了帮助运行 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更低版本的客户,我们列出了 32 位计算机值。32-bit computer values are listed for the assistance of customers running SQL Server 2014 (12.x)SQL Server 2014 (12.x) and earlier. 建议对 32 位计算机上运行的 SQL ServerSQL Server 实例使用最大工作线程数 1,024。We recommend 1,024 as the maximum number of worker threads for an instance of SQL ServerSQL Server that is running on a 32-bit computer.

    2SQL Server 2017 (14.x)SQL Server 2017 (14.x) 起,对于内存小于 2GB 的计算机,“默认最大工作器数”值除以 2。2 Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x), the Default Max Workers value is divided by 2 for machines with less than 2GB of memory.

    提示

    有关使用 64 个以上的 CPU 的建议,请参考 在具有超过 64 个 CPU 的计算机上运行 SQL Server 的最佳做法For recommendations on using more than 64 CPUs, refer to Best Practices for Running SQL Server on Computers That Have More Than 64 CPUs.

  • 如果所有工作线程因为长时间运行的查询而处于活动状态, SQL ServerSQL Server 可能停止响应,直到一个工作线程完成并变成可用。When all worker threads are active with long running queries, SQL ServerSQL Server might appear unresponsive until a worker thread completes and becomes available. 虽然这不是缺点,但有时用户可能并不希望如此。Although this is not a defect, it can sometimes be undesirable. 如果进程显示为停止响应并且不再处理新查询,则将使用专用管理员连接 (DAC) 连接到 SQL ServerSQL Server ,并关闭此进程。If a process appears to be unresponsive and no new queries can be processed, then connect to SQL ServerSQL Server using the dedicated administrator connection (DAC), and kill the process. 为避免此种情况发生,请增大最大工作线程数。To prevent this, increase the number of max worker threads.

SecuritySecurity

权限Permissions

默认情况下,所有用户都具备不带参数或仅带第一个参数的 sp_configure 的执行权限。Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default. 若要使用两个参数执行 sp_configure 来更改配置选项或运行 RECONFIGURE 语句,用户必须拥有 ALTER SETTINGS 服务器级别权限。To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, a user must be granted the ALTER SETTINGS server-level permission. ALTER SETTINGS 权限由 sysadmin 和 serveradmin 固定服务器角色隐式拥有。The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

使用 SQL Server Management StudioSQL Server Management StudioUsing SQL Server Management StudioSQL Server Management Studio

配置 max worker threads 选项To configure the max worker threads option

  1. 在对象资源管理器中,右键单击服务器并选择 “属性”In Object Explorer, right-click a server and select Properties.

  2. 单击 “处理器” 节点。Click the Processors node.

  3. 在“最大工作线程数”框中,键入或选择一个介于 128 到 32767 之间的值。In the Max worker threads box, type or select a value from 128 through 32,767.

提示

使用 max worker threads 选项配置可用于 SQL ServerSQL Server 进程的工作线程数。Use the max worker threads option to configure the number of worker threads available to SQL ServerSQL Server processes. max worker threads 的默认设置适用于大多数系统。The default setting for max worker threads is best for most systems. 不过,根据您的系统配置,有时将 max worker threads 设置为较小的值会提高性能。However, depending on your system configuration, setting max worker threads to a smaller value sometimes improves performance. 有关详细信息,请参阅本页的建议See Recommendations in this page for more information.

使用 Transact-SQLUsing Transact-SQL

配置 max worker threads 选项To configure the max worker threads option

  1. 连接到 数据库引擎Database EngineConnect to the 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”From the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute. 此示例说明如何使用 sp_configuremax worker threads 选项的值配置为 900This example shows how to use sp_configure to configure the max worker threads option to 900.

USE AdventureWorks2012 ;  
GO  
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'max worker threads', 900 ;  
GO  
RECONFIGURE;  
GO  

跟进:在配置“最大工作线程数”选项后Follow Up: After you configure the max worker threads option

执行 RECONFIGURE 后,此更改将立即生效,而无需重新启动 数据库引擎Database EngineThe change will take effect immediately after executing RECONFIGURE, without requiring the 数据库引擎Database Engine to restart.

另请参阅See Also

服务器配置选项 (SQL Server) Server Configuration Options (SQL Server)
RECONFIGURE (Transact-SQL) RECONFIGURE (Transact-SQL)
sp_configure (Transact-SQL) sp_configure (Transact-SQL)
用于数据库管理员的诊断连接Diagnostic Connection for Database Administrators