Troubleshooting Resource Governor

If there is no classifier UDF available for classification then all sessions will automatically go to the default workload group. After you create a classifier UDF, you need to verify that it is registered with Resource Governor and that the in-memory configuration is updated.

Creating, registering, and enabling a classifier UDF is a three step process:

• First, you need to create the function.

  CREATE FUNCTION function_name() RETURNS <something> 
  WITH SCHEMABINDING
  

• Second, you need to register the function with Resource Governor.

  ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=schema_name.function_name)
  

• Third, you need to update the Resource Governor in-memory configuration.

  ALTER RESOURCE GOVERNOR RECONFIGURE
  

The first thing that you should do when troubleshooting classification is verify that the function that you created is registered with Resource Governor and the configuration was updated. Use the following query to obtain the schema name (schema_name) and classifier function name (function_name) for the classifier UDF that Resource Governor is currently using.

USE master
SELECT 
      object_schema_name(classifier_function_id) AS [schema_name],
      object_name(classifier_function_id) AS [function_name]
FROM sys.dm_resource_governor_configuration

You can use the preceding approach to troubleshoot a scenario where you changed the classifier UDF but Resource Governor is still using the previous function logic to classify sessions. This behavior indicates that the changes that you made were not applied to the in-memory configuration.

You can create a very complex classifier function that does not produce the expected results or is resource intensive. If you have done the basic troubleshooting, you then need to verify that the function logic is sound. A worst-case scenario is one in which a coding flaw results in an infinite loop or a runaway query.

You can use a Dedicated Administrator Connection (DAC) to troubleshoot a poorly written classifier function because a DAC is not subject to classification and can be used while Resource Governor is running and classifying incoming sessions. For more information, see Using a Dedicated Administrator Connection.

You can obtain information about the classifier function by querying the following:

• sys.dm_exec_query_stats. (contains statement information but not the actual function)

• sys.dm_exec_sql_text (use in conjunction with the sql_handle obtained from sys.dm_exec_query_stats)

• PreConnect:Starting Event Class (provides ID and name of classifier function)

In this scenario, a user reports performance degradation and you suspect that the request count is throttled.

The first thing you need to do is verify whether request count throttling is explicitly configured for the group that the user is in. You do this by checking the group membership of the user to see whether the GROUP_MAX_REQUESTS setting is enabled. If GROUP_MAX_REQUESTS is not enabled, there is no explicit request count throttling. You should investigate further by taking the following steps.

• Query sys.dm_os_waiting_tasks to see if any requests are waiting on the RESMGR_THROTTLED wait type. The presence of this wait type indicates request count throttling.

• Start Performance Monitor and collect data using the Queued requests and Active requests counters. A non-zero Queued request count indicates request throttling.

• Check to see if the Active requests value matches the GROUP_MAX_REQUESTS setting. If the Active requests value is higher than the GROUP_MAX_REQUESTS setting, the group may have requests that cannot be throttled (for example, open transactions).

• If Queued requests is zero, check the Active requests of all the workload groups sharing the same resource pool because the pool may have been overloaded with too many requests.

If you have a policy that is driven by Resource Governor event generation you can use the event that is generated when the maximum CPU limit is reached.

In this scenario, you want to determine whether the maximum CPU limit (REQUEST_MAX_CPU_TIME_SEC) that you configured for detecting a query that uses too much CPU is too low.

The following actions will help you validate the CPU limit setting.

• Start a SQL Trace session and collect the CPU Threshold Exceeded event. When a user request reaches the maximum CPU usage limit, the server generates a SQL Trace event automatically. If your setting is too low, a high number of these events will be generated.

• Start Performance Monitor and collect data using the Max request cpu time (ms) counter. You can use the value of this counter as a guide for setting the appropriate limit for the workload group.

In this scenario, you suspect that CPU bandwidth is throttled because the CPU usage % performance counter is at or near the Resource Governor MAX_CPU_PERCENT setting. The following query returns the CPU usage % value for all the workload groups and resource pools for a SQL Server instance.

select * from sys.dm_os_performance_counters where counter_name = 'cpu usage %'

For more information, see sys.dm_os_performance_counters (Transact-SQL).

You can determine whether or not CPU bandwidth is throttled by performing the following checks on your system.

• Check the total server CPU utilization. If a load other than SQL Server is currently active, it may affect the query that you are troubleshooting.

• Check the distribution of CPU usage among the resource pools. A resource pool could be throttled because another pool has a high minimum value configured for CPU usage. Compare the counters for the expected (calculated CPU utilization) against the actual CPU utilization.

• Check the workload groups assigned to the resource pool in question. Load from other workload groups can affect users that are sharing the same pool.

• Check the distribution of the CPU usage across the schedulers. The query that you are investigating might be placed on a scheduler that contains long-running queries. In this case, the query may appear to be throttled but the actual issue is uneven distribution of load between schedulers.

• Check for possible cases where the workload is getting blocked by other sessions rather than being throttled by Resource Governor settings.

• Check the number of sessions currently running queries on the system. As the number of concurrent executing requests grows, SQL Server attempts to ensure that all of them are receiving at least some amount of CPU time to prevent CPU starvation.

In this scenario, you suspect that the size of grant memory is causing a query to run slowly.

Resource Governor enforces the maximum query memory limit by reducing grant memory so large queries can fit within the limit. If a query gets less than 100% of the memory grant, it may be necessary to spill temporary data and write it to disk, which can have a noticeable effect on performance.

You need to determine the percentage of large queries to set the appropriate maximum query size limit. The following actions will help determine the best settings:

• Query sys.dm_exec_query_memory_grants to see the current state of memory grants. The ideal_memory_kb column shows the ideal amount based on the cardinality estimate. The requested_memory_kb column shows the asking amount which could have been reduced after reaching the maximum query limit. If requested_memory_kb is significantly lower than ideal_memory_kb, then the query may end up spilling frequently (assuming that the cardinality estimate is correct).

• Start Performance Monitor and collect data using the Reduced memory grants/sec counter. The value of this counter represents the rate of memory grant count that received less than the ideal amount after hitting the maximum request size limit. Large queries may run much slower than those with an ideal amount because they need to spill to the disk to stay within the memory limits.

To alleviate a memory grant problem, you may have to increase the pool size limit or the maximum memory size limit.

In this scenario, a query fails with a memory grant timeout error.

Both the total number of active memory grant requests and memory limits specified on the workload group and resource pool definitions can play a role in memory grant timeouts. If a single resource pool is shared by multiple resource groups, the number of concurrent queries in other groups may also affect the memory grant timeouts.

The following actions will help determine the best resource pool settings:

• Query sys.dm_exec_query_memory_grants to see the number of memory grants and waiting queries on this group and pool.

• Query sys.dm_exec_query_resource_semaphores to see the total granted memory and targets.

If grant memory usage is greater than the available memory space, then you can consider increasing the resource pool size limit.

A query fails with an out of memory error.

Basic Troubleshooting

Advanced Troubleshooting

In this scenario, you suspect that a query is running slowly because of a suboptimal query plan. The query optimizer may generate a suboptimal query plan if it does not get enough memory because of a low memory limit setting for a resource pool.

The following actions will help determine the best resource pool settings:

• Get data from the Query optimizations/sec counter to see whether the workload group has a high number of query compiles.

• Get data from the Suboptimal plans/sec counter to see if the query optimizer is frequently producing suboptimal plans.

If either of the preceding conditions exists, consider increasing the memory limit of the resource pool.