491
Chapter 19: Confi guring SQL Server
19
Max Worker Threads
SQL Server is a multithreaded application, meaning that it can execute on multiple proces-
sors concurrently for increased performance. Multithreaded applications also enable more
effi cient use of a single processor because this enables another task to execute while a
task waits for a process that doesn’t use the CPU to fi nish. The threads are designed as
follows:■ (^) A thread for each network connection.
■ (^) A thread to handle database checkpoints.
■ (^) Multiple threads to handle user requests. When SQL Server handles a small num-
ber of connections, a single thread is assigned to each connection. However, as
the number of connections grows, a pool of threads handles the connections more
effi ciently.
Depending on the number of connections and the percentage of time those connections are
active (versus idle), making the number of worker threads less than the number of connec-
tions can force connection pooling, conserve memory, and improve performance.
In Management Studio, the max worker threads option is set by typing or selecting a
value in the Maximum worker Threads box on the Server Properties Processor tab (refer to
Figure 19-7 earlier in the chapter).
From code, the maximum number of worker threads is set by means of the sp_configure
stored procedure and the max worker threads option. For example, the following code
sets the max worker threads to 128.
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max worker threads', 128;
RECONFIGURE;
SQL Server service must be restarted for the max worker threads setting to take effect.
Best Practice
On SQL Server 2008 and SQL Server 2012, the default value of 0 for the max worker threads property
provides the best performance of SQL Server. This default value indicates that SQL Server automati-
cally determines the correct number of active worker threads based on user requests.
If you do need to change the default value, it is not recommend to set the max worker threads option
to a small value because that may prevent enough threads from servicing incoming client requests in a
timely manner and could lead to “thread starvation.” However, do not set the max worker threads
option to a large value because that can waste memory because each active thread consumes 512KB
on 32-bit servers and up to 4MB on 64-bit servers.
c19.indd 491c19.indd 491 7/30/2012 5:42:55 PM7/30/2012 5:42:55 PM
http://www.it-ebooks.info