How good is resource governor?

Question

I have a virtualized SQL server 2017 with a single application.
It has 32 Gb ram and 16 processors.
The application has high CPU pattern, sometimes reaches the 100% during a few seconds.
When reaches 100% the machine gets stalled.
I’m thinking using resource governor to guarantee the CPU never reaches 100%
I need news from the ground; the good and less good about resource governor.

Answer 1

It is completely normal for SQL Server to use 100% of CPU for short periods of time. That is not an issue.

Resource governor only controls interaction between 2 resource groups, not when only 1 resource group is used. This will not help your situation.

SQL Server is rarely CPU dependent. High CPU is generally an indication of another problem, like our of memory or slow disk IO causing the high CPU.

Answer 2

Resource Governor is only available on Enterprise Edition. Do you really have Enterprise on a VM with that humble configuration?

Anyway, if the machine goes into nirvana when SQL Server hits 100% CPU there are more things involved. You need to check the SQL Server errorlog and the Windows event log for clues.

How big is the database? 32 GB of RAM is not a lot.

Answer 3

Hi @Miguel Gavinhos

By using Resource Governor, you can:

1. Provide multitenancy and resource isolation on single instances of SQL Server that serve multiple client workloads. That is, you can divide the available resources on a server among the workloads and minimize the problems that can occur when workloads compete for resources.
2. Provide predictable performance and support SLAs for workload tenants in a multi-workload and multi-user environment.
3. Isolate and limit runaway queries or throttle I/O resources for operations such as DBCC CHECKDB that can saturate the I/O subsystem and negatively impact other workloads.
4. Add fine-grained resource tracking for resource usage chargebacks and provide predictable billing to the consumers of the server resources.
   Clearly, resource governor is not a good choice.
   High CPU usage can affect processing speed, which can slow down your computer, and if the CPU reaches 100% it can cause extremely slow operation or even crash.
   Databases can be monitored using SQLServer Profiler

Best regards
Niko

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.