What Is A Worker Threads In SQL Server?

Worker threads are the agents of SQL Server which are scheduled in CPU and they carry out the tasks. Memory for Worker threads come from Non-Buffer Pool region of SQL Server. – MAX Worker Threads

Purpose Of Max Worker Thread 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,

The max worker threads option configures the number of worker threads that are available to SQL Server processes. The default value for max worker threads is 0. This enables SQL Server to automatically configure the number of worker threads at startup. The default setting is best for most systems. However, depending on your system configuration, setting max worker threads to a specific value sometimes improves performance.

If you leave ‘Max. Worker threads’ to 0 then SQL Server will decide the worker thread count based on formula below:

Max Worker Threads For 32 bit operating system:

Total available logical CPU’s <= 4 : max worker threads = 256

Total available logical CPU’s > 4 : max worker threads = 256 + ((logical CPUS’s – 4) * 8)

Max Worker Threads For 64 bit operating system:

Total available logical CPU’s <= 4 : max worker threads = 512

Total available logical CPU’s > 4 : max worker threads = 512 + ((logical CPUS’s – 4) * 16)

Before You Begin

Limitations and Restrictions

• When the actual number of query requests 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, SQL Server pools the worker threads so that the next available worker thread can handle the request.

Recommendations

• This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician.
• 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 requests, which improves performance.
• The following table shows the automatically configured number of max worker threads for various combinations of CPUs and versions of SQL Server.

• When all worker threads are active with long running queries, SQL Server might appear unresponsive until a worker thread completes and becomes available. Although this is not a defect, it 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.

If You Have Set Max. Worker Thread To 0, You Can Check The Worker Thread Count Calculated By Sql Server Using The Query

SELECT max_workers_count FROM sys.dm_os_sys_info

When a request is received, SQL Server will try to locate a idle thread to schedule the request. If an idle worker was not located, new worker will be created. If all workers have been created and none is idle, then the request is queued which will be picked up by any worker thread from the pool of worker threads created.

Query Below Gives The Amount Of Worker Threads Created At The Moment In Your Sql Server:

SELECT SUM(current_workers_count) as [Current worker thread] FROM sys.dm_os_schedulers

General recommended to leave Max. worker threads to 0.

Configure Using Sql Server Management Studio

To configure the max worker threads option

• In Object Explorer, right-click a server and select Properties.
• Click the Processors node.
• In the Max worker threads box, type or select a value from 128 through 32767.

Use the max worker threads option to configure the number of worker threads available to SQL Server processes. The default setting for max worker threads is best for most systems. However, depending on your system configuration, setting max worker threads to a smaller value sometimes improves performance.

Configure Using Transact-Sql

To configure the max worker threads option

• Connect to the Database Engine.
• From the Standard bar, click New Query.
• Copy and paste the following example into the query window and click Execute. This example shows how to use sp_configure to configure the max worker threads option to 900.

Tsql

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

The change will take effect immediately without requiring the Database Engine to restart.