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 as 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 to everyone who has signed for my consulting engagement to not to change this thread count.

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

, , ,
Previous Post
SQL SERVER – How to Get Started with Docker Containers with Latest SQL Server?
Next Post
SQL SERVER – Priority Boost and SSMS 18

Related Posts

Leave a Reply

Menu