SQL Server - max worker threads (max user connections)

最新推荐文章于 2025-07-04 15:45:46 发布

envykok

最新推荐文章于 2025-07-04 15:45:46 发布

阅读量389

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL Daily Maintanence 文章标签： sql server user sqlserver statistics go object

本文链接：https://blog.youkuaiyun.com/envykok/article/details/5970728

SQL Daily Maintanence 专栏收录该内容

31 篇文章

订阅专栏

本文介绍如何获取和设置 SQL Server 的 maxworkerthreads 参数，包括通过 sp_configure 存储过程进行配置的方法，并提供适用于不同处理器数量的计算公式。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Get the current value:




sp_configure 'show advanced options',1 ; 



GO 



RECONFIGURE; 



GO

sp_configure 'max worker threads' -SHOW MAX WORKER THREADS VALUE

Set the new value to 512 :

Solution1 :

sp_configure 'max worker threads', 512

RECONFIGURE;

GO

Solution2 :

How to calculate:

To address the previous discussion, adding more CPU does not necessarily double the Worker Count. In fact, the logic behind this simple principle is as follows:

For x86 (32-bit) upto 4 logical processors max worker threads = 256
For x86 (32-bit) more than 4 logical processors max worker threads = 256 + ((# Procs – 4) * 8)
For x64 (64-bit) upto 4 logical processors max worker threads = 512

For x64 (64-bit) more than 4 logical processors max worker threads = 512+ ((# Procs – 4) * 8)

http://technet.microsoft.com/en-us/library/ms187024.aspx

http://loadrunnertnt.blogspot.com/2007/05/monitors-ms-sql-server.html

User Connections (SQLServer: General Statistics object): this is the value of number of users connected to this database. Note that this value is the total number of user connections and not the total number of users. By default, SQL Server is configured for 255 user connections . If this value is exceeded, then increase the value of "Maximum Worker Threads" to a number higher then 255. As a rule, this value should be higher then the number of user connections. You can also set the maximum concurrent user connections to ‘0’. Which means that the number of connections is limited only by the SQL Server maximum.