question

AlphonseG8302 avatar image
0 Votes"
AlphonseG8302 asked AlphonseG8302 commented

Limit SQL Server CPU usage for Login

Using SQL Server 2016 Standard Edition. Is there any way to limit CPU usage for a specific login?

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered AlphonseG8302 commented

No. But if you cough up the money for a license for Enterprise Edition, you will have Resource Governor at your disposal. But this component is not available in Standard Edition.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks to everyone.
Enterprise is not an option. I'm just the guy that's responsible for the on premises app and database. There is also a web app the connects to the database. The web app is extremely poorly written regarding data access. Like, 130 redundant database calls to load one page. I have no control over that, but I'm the guy that everyone calls when the db becomes non-responsive. I thought that if was some way to limit resources for that user, it would force them to fix it.

0 Votes 0 ·

Hm, I think it is a good thing that you don't have Enterprise. :-)

While poorly written applications are a pain, I don't think the right way to trigger an improvement is to press the go-slower button on the server. You will have to find the right political path to get that app fixed. (And, yes, that can be a lot more difficult to solve than purely technical problems.)

0 Votes 0 ·

Thanks. I do agree that it's not the 'right' way. Sometimes, a little of the 'not so right' way can help highlight an issue.

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @AlphonseG8302,

Using SQL Server 2016 Standard Edition. Is there any way to limit CPU usage for a specific login?

No, if you upgrade your SQL Server 2016 from standard edition to enterprise edition. You can using SQL Server Resource Governor feature.

118388-screenshot-2021-07-28-094810.jpg

SQL Server Resource Governor is a feature that you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU, physical I/O, and memory that incoming application requests can use.

Please refer to MS document Resource Governor and How To Limit a User’s Resource Usage Using Resource Governor.


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.





5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

As Erland said, not in Standard edition.

However, SQL Server rarely uses large amount of CPU, unless there is a problem with a query. Rather than try to limit CPU, you should look at fixing the bad queries.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.