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.
Here is the table which lists all the automatically configured number of max worker threads for various combination of CPUs.
| Number of CPUs | 64-bit computer |
|---|---|
| <= 4 processors | 512 |
| 8 processors | 576 |
| 16 processors | 704 |
| 32 processors | 960 |
| 64 processors | 1472 |
| 128 processors | 4480 |
| 256 processors | 8576 |
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 CPUs | 64-bit computer |
|---|---|
| <= 4 processors | 512 |
| > 4 processors and <= 64 processors | 512 + ((logical CPU’s – 4) * 16) |
| > 64 processors | 512 + ((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)
