0

I am using SQL Server SE 2019, on a Windows Server 2019 (with SSD drive) that run IIS 10. On top of IIS 10 run an ASP.Net application, that has to be considered AS IS (I can't modify it).

Lately tourn out that the ASP.Net application may become a bit too much chatty, and start making many DB queries in batches (few burst of 20-30 query/s). Under heavy load this can become an issue, so I would like to improve the system performance. SQL Server was installed without any particular configuration (default from installation wizard).

I would like to konw if there is some known configuration I can tune (SQL Server/IIS) that may optimize the scenario in which there are many small and fast queries (~5ms per query) sent in batches of 20-30 query/s. So that I can improve the situation in which the ASP.Net application is under heavy load and start spamming a lot of fast queries to DB

Improve this question
6
  • "On top of IIS 10 run an ASP.Net application, that has to be considered AS IS (I can't modify it).' - Fair, what about the database server?...can you move it to another machine? It's usually not a good idea to split resources of the same machine between a database server and other things like applications. 30 queries a second is nothing to be concerned with, if they're properly tuned. Without seeing some examples of these queries and their execution plans, we wouldn't be able to tell you if they are tuned or if there's room for improvement. Feel free to add the execution plans to your Post.
    – J.D.
    Commented Jul 31, 2023 at 18:58
  • @J.D. you are right, but I can't split machine, on the other hands the machine is quite perofrmant. My concern is that the 30req/s are made for each IIS application request. So under load the requests count grow up fast
    – Skary
    Commented Jul 31, 2023 at 19:39
  • 1,000 requests / second is nothing to be concerned with when the queries are well tuned. But again, we can't say that's your case without the aforementioned information I mentioned.
    – J.D.
    Commented Jul 31, 2023 at 19:45
  • "on the other hands the machine is quite perofrmant" - It's still not a good way to design an application stack. Unlike other application servers, database systems are designed to hog the resources of the machine they're on, even if they don't necessarily need those resources at the moment (such as Memory and Disk). This is to proactively keep them ready to maximize future performance. So even on an over-provisioned machine, there's a conflict of how resources are balanced and consumed, by design.
    – J.D.
    Commented Jul 31, 2023 at 19:46
  • 1
    @J.D. probably you are right and the "many" queries are a non issue here. I look better to a stress test (~100 req/s to ASP.NET appication) and SQL Server ramp up to 2000 batch request/s but the real issue seems more ASP.NET CPU usage, that goes up to 90% (SQL is arround 5%)
    – Skary
    Commented Aug 2, 2023 at 9:14

1 Answer 1

Reset to default
2

There is probably little you can do in terms of SQL Server config. I would assume that 30*5ms of work each 1000ms isn't a problem in that regard, particularly if the queries are mostly reads that don't hold locks which will interfere with each other (so some could be happening in parallel in which case that 30*5ms is total processing time with wall-clock time consumed being lower).

There are some settings to try control the number of concurrent connections but they carry warnings like “This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server professional.” so playing with them is not recommended unless you have very specific reason to think they are relevant to your situation and won't make something else perform badly or error out.

Ensuring connection pooling is used effectively to reduce the extra overheads of bringing up and closing down excess connections would be something to look at if you could control the application, but in this instance you can't.

Ensuring that queries don't unnecessarily block each other is another factor, but again this constitutes controlling the app which is out of scope for your question (if the calls are to stored procedures you could potentially make tweaks to them without access to the application, but I wouldn't recommend that as you'd likely be in breach of your support arrangements with the application provider).

Under heavy load this can become an issue

Do you have any analysis of this actively being an issue? Either logged under normal use or through artificial load tests? If so, add those details to the question.

Improve this answer

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.