How good is resource governor?

Miguel Gavinhos 216 Reputation points
2022-08-30T10:55:50.533+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,801 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2022-08-30T12:18:46.32+00:00

    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.

    0 comments
  2. Erland Sommarskog 101.7K Reputation points MVP
    2022-08-30T21:50:36.977+00:00

    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.

    0 comments
  3. NikoXu-msft 1,911 Reputation points
    2022-08-31T03:16:30.897+00:00

    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.

    0 comments