MAX WORKER
THREADS
Use the max worker threads
option to configure the number of worker threads available to Microsoft SQL
Server processes.
Purpose of Max worker threads option:
Thread pooling helps optimize performance when large numbers
of clients are connected 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. The max worker threads
option enables SQL Server to create a pool of worker threads to service a
larger number of query request, which improves performance.
Calculating Max worker Threads:
The default value for max worker threads,
0, allows SQL Server to automatically configure the number of worker threads at
startup. This setting is best for most systems; however, depending on your
system configuration, setting max worker threads
to a specific value sometimes improves performance.
The max worker threads is based on the number of
processors…with help of the below formula we can calculate the max worker
threads.
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) * 16)
With help of the below DMV we can find the max workers threads count: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) * 16)
Select max_workers_count from sys.dm_os_sys_info
|
As per the Micro soft 1024 as the maximum for 32 bit SQL
Server.
When the actual number of query request is less than the
amount set in max worker threads,
one thread handles each query request. However, if the actual number of query
request exceeds the amount set in max worker threads,
SQL Server pools the worker threads so that the next available worker thread
can handle the request.
Upgrading from sql server 2000:
The default setting of max
worker threads in SQL Server 2000 was 255. Upgrading an
instance of the SQL Server 2000 Database Engine to a newer version retains the
configuration value for max worker threads.
When upgrading, we recommend changing the new instance max worker threads value to 0,
to allow the Database Engine to calculate the optimal number of threads.
How to configure the max worker threads:
From SSMS also we can configure the max worker threads…
Go to Server Node
----> Right Click and Select Property ----> Select Process and modify
setting under Worker Threads.
The max worker threads
option is an advanced option. If you are using the sp_configure
system stored procedure to change the setting, you can change max worker threads only when show advanced options is set to
1. The system must be restarted in order for the new setting to take effect.
| Note: When all worker threads are active with long running queries, SQL
Server may appear unresponsive until a worker thread completes and becomes
available. Though not a defect, this can sometimes be undesirable. If a
process appears to be unresponsive and no new queries can be processed, then
connect to SQL Server using the dedicated administrator connection (DAC), and
kill the process. To prevent this, increase the number of max worker threads. |
With help of the below DBCC we can find the num of tasks,
num of works, and active works etc…
DBCC SQLPERF(UMSSTATS)
The
following link having good example for Threadpool wait type…because of the lack
of worker thread we get the threadpool wait type…don't forget to check the below link...
Note: I
gather the above information from different internet links…
JHAPPY LEARNINGJ
