Databases Reference
In-Depth Information
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 (that you need to care about):
➤
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 (see below) 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 i nished 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. Waits and wait
types are covered later in this section.
The default max workers count is based on the CPU architecture and the number of logical proces-
sors. 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
2
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
2
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
Search WWH ::
Custom Search