2

I use 64-bit OS with two CPUs (each of them makes 7 logical processors) and with total number of logical processors equal to 14.

When I run this code I get 2048

SELECT max_workers_count FROM sys.dm_os_sys_info

On the other way, when I use the formula below I get totally different number which is 672

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)

Can you explain to me why I get different numbers? I tested it on another server and got exact same numbers for both cases.

EDIT

enter image description here

P.S Thanks to Dan Guzman for his suggestions to check server settings for max worker threads. That is what EXEC sp_configure 'max worker threads' returns:

enter image description here

Improve this question
3
  • where did you get that formula ?
    – Aleksey Vitsko
    Jan 1, 2020 at 11:37
  • I have 24 logical cores and I get correct result 832.
    – user168186
    Jan 1, 2020 at 11:53
  • 1
    @DanGuzman thank you for suggestion to check default settings. It seems that someone changed that value. The next question came into my mind: is this value makes any sense if max calculated value can only be 672? In other words, if I set the value to 3000 instead of 2048, will it make any sense as according to formula above max worker thread count is up yo 672?
    – Rauf Asadov
    Jan 1, 2020 at 13:56

2 Answers 2

Reset to default
8

It seems someone has changed the maximum worker threads setting to a non-default value, which will override the default calculated value. The current configured value can be determined with:

EXEC sp_configure 'max worker threads';

I'll add the value is sometimes set mistakenly as a knee-jerk reaction to address symptoms rather than the underlying cause. Some valid reasons for configuring the value are called out in the documentation but the default calculation is appropriate for most workloads.

Improve this answer
7
  • Can it happen that SQL Server uses those 2048 threads? I wonder whether it will use up to 672 or will cross the line and use over 672.
    – Rauf Asadov
    Jan 1, 2020 at 17:50
  • @RaufDBA, the max_workers_count is the limit regardless of whether it was configured manually or automatically.
    – Dan Guzman
    Jan 1, 2020 at 18:12
  • Thank you. Now it is clear
    – Rauf Asadov
    Jan 1, 2020 at 18:19
  • What happens when the max limit has reached and a new query arrives?
    – variable
    May 14 at 12:13
  • @variable, when no worker threads are available. those tasks encounter THEADPOOL waits until a worker is available.
    – Dan Guzman
    May 14 at 12:55
2

The formula works for me, I have 32 logical cores, 512+((32-4)*16) = 960 which is exactly what I get from 

SELECT max_workers_count FROM sys.dm_os_sys_info

Could you go into Task Manager -> Performance Tab -> CPU, and post here a screenshot of basic CPU information from your system (sockets, cores, logical processors, etc) ?

Improve this answer
1
  • Sure. I've added
    – Rauf Asadov
    Jan 1, 2020 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.