SQL SERVER – Default Worker Threads Per Number of CPUs

During my recent SQL Server Performance Tuning Practical Workshop, someone asked, how do they know what is the automatically configured max worker threads for their machine. This is a good question. Microsoft has already answered this question so we will follow their guidance.

SQL SERVER - Default Worker Threads Per Number of CPUs workercount

Here is the table which lists all the automatically configured number of max worker threads for various combination of CPUs.

Number of CPUs64-bit computer
<= 4 processors512
8 processors576
16 processors704
32 processors960
64 processors1472
128 processors4480
256 processors8576

It is absolutely true that no matter how many CPU you have as long as you are on SQL Server 64 bit computer you will have a minimum of 512 threads.

The formula for the threads is also very simple. Using the following formula, you can find threads for any number of CPUs.

Number of CPUs64-bit computer
<= 4 processors512
> 4 processors and <= 64 processors512 + ((logical CPU’s – 4) * 16)
> 64 processors512 + ((logical CPU’s – 4) * 32)

Okay, so now you know the basics on this subject, let me answer one of the most popular questions on this subject.

Question: What is the ideal number of threads for any SQL Server?

Answer: There is no fixed number for the threads which can be called magic threads. I personally prefer that you do not change the thread from the default count to any other number.

If the worker threads run out, SQL Server just waits till the existing worker threads are available. If you increase this number beyond the capacity of what your SQL Server engine can handle, you can lead to a totally different performance problem. I always advise everyone who has signed for my consulting engagement not to change this thread count.

You can connect me on Twitter for follow-up questions.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL CPU, SQL DMV, SQL Scripts, SQL Server
Previous Post
SQL SERVER – How to Drop All the Hypothetical Indexes of Database?
Next Post
SQL SERVER – When You Should Not Attend SQL Server Performance Tuning Practical Workshop for EVERYONE?

Related Posts

Leave a Reply