0

CEO wants "solutions" to CPU on the SQL Server always being near max use. I figured out that they have all the customers calling an API to hit this one stored procedure to the tune of 80,000 batch call per second as seen in activity monitor.

While I don't see any actual issues just the CPUs being constantly full speed ahead, the CEO is still asking for "solutions" to this problem.

Is there a way to check if there is a connections limit being hit or are you able to like spin up a second instance of SQL Server to mitigate the number of CPUs getting hurt? Basically, the CEO wants to see the CPUs not constantly getting nailed in some magical way.

The index for that one table for that simple stored procedure is basically perfect. It is only using the columns it needs as well. The SQL is simple so I can't optimize it any more either.

It's hosted on a 64-core machine with 800 GB of memory (only at 5% usage so no issue there). About 50+ of the cores are showing 100% usage so about 75% constantly in use. To me as well it looks fine because, hey, your software is calling the same stored procedure 80k times per second. So yeah, the CPU is in use. But to him any solution that involves reducing the number of calls is not acceptable. He personally mentioned the "running a second instance" idea but that isn't exactly a magic answer.

The current CPU total usage is only about 70% with 50 cores in Task Manager at 100% and the other 14 or so are just barely in use or idle. So not sure we would see more throughput if we added more cores. I'm more so worried about some SQL connection limit and how to maybe solve that if that is an issue at all (on top of how to magically "solve" this issue that they think is an issue).

Improve this question
4
  • What kind of system does that database instance support? I ask because if it's some kind of ecommerce and your CPU consumption is at 90% those 10% left could not be enough to fulfill the traffic generated by some sudden discount offer that the market may come up with all of a sudden. But if it's a really, really predictable kind of system with no chance of a sudden unplanned usage, you can show him it's safe by presenting a report with the monthly consumption for the last few months.
    – Ronaldo
    Commented Feb 18, 2023 at 19:44
  • Ask your CEO if you double the number of cores/servers and find CPU stays at near 100% but throughput doubles to 160K/sec, is that still a problem or is it better? My gist is you need an SLA.
    – Dan Guzman
    Commented Feb 18, 2023 at 20:47
  • No worries, I understand. This is probably too broad of a situation that can easily be answerable in a single question, but if you wanted to try to make the procedure more performant (or at least verify it's as performant as possible) then you can post the code and execution plan (which you can upload on Paste The Plan). That's more targeted of a question. "the other 14 or so are just barely in use" - It sounds like you're not running at 100% then / don't necessarily have a performance problem, but hard to say without a deep analysis.
    – J.D.
    Commented Feb 18, 2023 at 22:02
  • 1
    Consider memory optimized tables and stored procedures at that high throughput. learn.microsoft.com/en-us/sql/relational-databases/…
    – David Browne - Microsoft
    Commented Feb 19, 2023 at 0:26

1 Answer 1

Reset to default
3

It might be worth considering a caching layer between the application and the database to reduce the load on the database server. Do you see a lot of requests with the same parameter values? How up-to-date does the data returned need to be?

It's possible some of the API calls are due to a particular customer abusing your API. This is probably unintentional - maybe calling your API inside a loop instead of outside etc. Setting throttling/rate limits might help protect your database server and maintain QoS for other clients. Or extra $$$ above a certain number of requests. This also gives incentives for clients to optimize their code.

If you are in the cloud you might be able to migrate to an instance type with faster CPU cores. Or you could consider more CPU cores. You might also be able to reduce load with an always on readable secondary - but you probably need to make some app changes to take advantage of this and its extra licensing cost.

At very high volumes of requests, every little micro-optimization is important. There might be options to optimize the code you haven't thought about.

Improve this answer
1
  • PS. You might benefit from a monitoring tool if you are using activity monitor. I created DBA Dash which is totally free and open source.
    – David Wiseman
    Commented Feb 19, 2023 at 8:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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