SQL Server - max worker threads (max user connections)

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.