SQL Server - Resource Governor part 3

The Resource Governor is a feature of SQL Server that can manage how much system resources different loads are allowed to use and how. It is possible to limit/monitor/prioritize the amount of CPU and memory that a specific load can use on an instance.

In previous parts of this series, I've done a quick review of what the Resource Governor can do and how to set it up. But once it's set up, it can be useful to be able to follow up on how it's set up and how it's used.

To see how the Resource Governor is configured, SQL Server has three built-in dynamic management views for this:

sys.dm_resource_governor_resource_pools

- Shows information on the use of the various resource pools and statistics on them.

sys.dm_resource_governor_workload_groups

- Displays statistics about the workload group and the configuration currently in use.

sys.dm_resource_governor_configuration

- Shows the id of the classifier function in use and whether the current configuration is read into memory and used by the Resource Governor. Otherwise, a restart or running the RECONFIGURE statement for the Resource Governor is required.

To see the configuration of the different resource pools, you can for example run the query below against sys.dm_resource_governor_resource_pools. It also returns how much memory is currently allocated:

SELECT name,
       min_cpu_percent,
       max_cpu_percent,
       min_memory_percent,
       max_memory_percent,
       max_memory_kb,
       used_memory_kb,
       target_memory_kb,
FROM sys.dm_resource_governor_resource_pools;

The same view also gives indications whether the configuration of the resource pool is correct or whether something should be changed.

SELECT name,
       total_memgrant_timeout_count,
       out_of_memory_count,
       memgrant_waiter_count 
FROM sys.dm_resource_governor_resource_pools;

total_memgrant_timeout_count

- Counter showing all calls that have timed out while waiting for memory allocation. Indicates that the resource pool may have too little memory allocated.

out_of_memory_count

 - Shows the number of questions that have failed due to insufficient memory. Check if MAX_MEMORY_PERCENT should be increased or if it is possible to rewrite the questions to be less memory intensive. It may also be because there is too little memory on the whole instance so raising MAX_MEMORY_PERCENT may not help.

memgrant_waiter_count

- Shows in real time how many connections in the resource pool are waiting for memory. If there are many, it indicates that the resource pool has too little memory allocated.

sys.dm_resource_governor_workload_groups

- Contains information and statistics for the calls received for the different workload groups.

SELECT
    name,
    active_request_count,
    total_request_count,
    total_suboptimal_plan_generation_count,
    total_reduced_memgrant_count
 FROM sys.dm_resource_governor_workload_groups

active_request_count

- Returns the current number of calls for each group.

total_request_count

- The cumulative number of calls for each group.

total_suboptimal_plan_generation_count

- Shows how many times the instance has used an inferior query plan due to workload settings and/or lack of memory. If it is a large number, the queries should be adjusted or the MAX_MEMORY_PERCENT setting should be revised.

total_reduced_memgrant_count

- Shows how many times a query could not get as much memory as it calculated it needed. Often this leads to increased CPU usage and/or increased tempdb usage. Also for this, the memory settings and queries should be checked. It may also indicate that the server itself has too little memory.

There are also a number of other DMVs related to the Resource Governor that may be useful to know about. For example, sys.dm_exec_session shows which resource group each call belongs to in the group_id column. The sys.dm_exec_cached_plans shows which resource pools the respective cached query plan uses.
There is much more to be gleaned from the above DMVs depending on what settings are made but for the simple examples we have done so far, the above is a good start. The Resource Governor is a powerful feature of SQL Server that we will certainly have occasion to return to and delve into.

/Björn