SQL Server 2019 Min Worker Thread Calculations

Question

Post reply

SQL Server 2019 Min Worker Thread Calculations

frederic.boistuaud

SSC-Addicted

Points: 456
More actions
August 1, 2022 at 7:55 am

#4070773
I have looked at
https://www.sqlshack.com/max-worker-threads-for-sql-server-always-on-availability-group-databases/
And
https://sqlmastersconsulting.com.au/SQL-Server-Blog/alwayson-availability-groups-what-not-to-do-when-adding-databases/
I'm trying to calculate minimum required worker threads on the primary using this formula:
SELECT AG = D * (LCWT + (LSWT * SRC)) +MHWT
Knowing that I have the following setup:
- 3 VMS (1 sql instance instance per vm) , one is primary the other 2 are secondaries.
- 4 AGS, (ag01, ag02, etc...)
- 197 databases, in total, i.e. some are are on ag1, some on ag2, etc...
I can't find a definition for MessageHandlerWorkerThread, example I have seen always a value set to 1.
Could someone confirm that the following calculations are correct:
--> Max available threads:
DECLARE @CpuCount AS INT = (
SELECT I.cpu_count
FROM sys.dm_os_sys_info I
)
SELECT 512 + ((@CpuCount - 4) * 16 )
--> 640 on my system
--> Min worker thread for AG:
DECLARE @D AS INT = 197, -- Nb of databases
@LCWT AS INT = 1, -- LogCapture Worker Thread
@LSWT AS INT = 1, -- LogSend Worker thread
@SRC AS INT = 2, -- Secondary Replica count
@MHWT AS INT = 1; -- Message Handler Thread
SELECT @D * (@LCWT + (@LSWT * @SRC)) + @MHWT
--> 592 on my system.
As 640>592 we are good for now, I think.
More importanly we need to add an extra 70 databases, or so.
The second calculation would give me 802 threads.
To have some slack say I wanted 820 max threads, I would require 24 cpus?
DECLARE @CpuCount AS INT = 24
SELECT 512 + ((@CpuCount - 4) * 16 )
--> 832
Hope someone can confirm for me thanks
- This topic was modified 2 years, 3 months ago by frederic.boistuaud.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

why haven't you launched that question at the individual sites ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

frederic.boistuaud SSC-Addicted Points: 456 More actions · Answer 2

Sorry,

Don't understand.

What do you mean by 'individual sites'?

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

With most articles, you can pose questions.

As these people have tested the subject, they may already have the answer ready or even blog about it later on.