How to estimate cpu based on peak rate of query requests?
By default, SQL Server allows a maximum of 32767 concurrent connections which is the maximum number of users that can simultaneously log in to the SQL server instance.
I am developing 10 web apis that will query the sql server using their own logins (so max 10 logins, plus admin/devs). Expected number of concurrent logins is not beyond 20.
However each login (api) can make several (maybe 500) concurrent query requests. So effectively 10 apis * 500 req = 5000 concurrent query requests. There will be times when there are less or no requests.
Assuming there is sufficient memory and disk io capability, I am planning the cpu requirements.
I understand sql request is assigned to the worker thread and based on number of processors there is a certain number of default worker threads. Presently my dev machine has 24 processors so the default max worker thread is 832.
Assuming cost threadhold (of 40) may be exceeded by the queries, which means the sql server may decide to use parallelism (max dop).
Assuming MAX DOP = 1 then 832 requests can be handled at a time.
Assuming MAX DOP = 4 then 208 requests can be handled at a time.
Any query requests beyond this will have to wait until it is allocated a worker thread.
So to ensure peak load of 5000 requests can be satisfied, is it correct to estimate that I will require roughly atleast 145 cpus?
((145-4)*32)+512 = 5024
There's no way to estimate core count based on number of requests. 500 inexpensive queries may execute concurrently in a few milliseconds of CPU time or could take several hours of CPU time.
Depending on your response time requirements, and the cost of those concurrent queries, it could require a 4-core machine or a massive machine.
A few years ago, Nick Craver https://nickcraver.com/blog/2016/03/29/stack-overflow-the-hardware-2016-edition/ . We've made a few upgrades to Stack Overflow infrastructure since 2016, but consider the traffic volume of Stack Overflow and the Stack Exchange network (even back in 2016), compared to the hardware in use. At the time, it was 12 cores for Stack Overflow and 8 cores for the Stack Exchange network.