Threads & Schedulers

 When an application authenticates to SQL Server it establishes a connection in the context of a session, which is identified by a session_id. 

When an execution request is made within a session, SQL Server divides the work into one or more tasks and then associates a worker thread to each task for its duration. Each thread can be in one of three states. 

 ➤ Running — A processor can only execute one thing at a time and the thread currently executing on a processor will have a state of running.  

➤ Suspended — SQL Server has a co-operative scheduler so running threads will yield the processor and become suspended while they wait for a resource. This is what we call a wait in SQL Server.  

➤ Runnable — When a thread has finished waiting, it becomes runnable which means that it’s ready to execute again. This is known as a signal wait.  

 

If no worker threads are available and max worker threads has not been reached, then SQL Server will allocate a new worker thread. If the max worker threads count has been reached, then the task will wait with a wait type of THREADPOOL until a thread becomes available.  

 

The default max workers count is based on the CPU architecture and the number of logical processors. The formulas for this are as follows: 

 For a 32-bit operating system: ➤ Total available logical CPUs <= 4 ➤ Max Worker Threads = 256  

➤ Total available logical CPUs > 4 ➤ Max Worker Threads = 256 + ((logical CPUs - 4)*8)  

 

For a 64-bit operating system: ➤ Total available logical CPUs <= 4 ➤ Max Worker Threads = 512  

➤ Total available logical CPUs > 4 ➤ Max Worker Threads = 512 + ((logical CPUs - 4)*16) 

 

 As an example, a 64-bit SQL Server with 16 processors would have a Max Worker Threads setting of 512 + ((16–4)*16) = 704.  

 

You can also see the max workers count on a running system by executing the following:  

SELECT max_workers_count FROM sys.dm_os_sys_info 

 

Each worker thread requires 2MB of RAM on a 64-bit server and 0.5MB on a 32-bit server, so SQL Server creates threads only as it needs them, rather than all at once. The sys.dm_os_workers DMV contains one row for every worker thread, so you can see how many threads SQL Server currently has by executing the following:  

SELECT count(*) FROM sys.dm_os_workers. 

 

Schedulers Each thread has an associated scheduler, which has the function of scheduling time for each of its threads on a processor. The number of schedulers available to SQL Server equals the number of logical processors that SQL Server can use plus an extra one for the dedicated administrator connection (DAC). You can view information about SQL Server’s schedulers by querying the sys.dm_os_schedulers DMV.