I work on a dotnet / WebForms web application that's load balanced over a number of web servers. The web servers connect to a SQL Server 2016 instance.
This web application includes several ways for end-users to request reports. Some of those reports take a lot of resources. I'm hoping to add a feature to the web app that says "Sorry, we're too busy to honor this request now. Please try again later."
Is there a good in-band way — a good query or SP — to ask SQL server how loaded it is at the moment? It happens that most overloads in our environment involve high cpu utilization.
If there's no good query, can anyone suggest an out-of-band way to do this reliably and simply? Maybe some sort of WMI operation.
Once I know how loaded the DBMS machine is, then I can decide whether to ask my end-users to come back later.
Edit. Two reasons to do this rather than add a server.
the user-frustration factor. If a request takes too long, the user may figure something's wrong and request it again. The way things work in web apps, SQL queries keep going even when the user has abandoned the request.
more servers cost money. The business I'm serving is self-funded. Managing peak load is as good a strategy as adding 24×7 base load capacity.