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.

Note:
If a DAC is not available for troubleshooting, you can restart the system in single user mode. Although single user mode is not subject to classification, you cannot diagnose Resource Governor classification while it is running.

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.
  

Note:
This event is also exposed as a server event notification so you can write a script that reacts to the event.

• 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.

Note:
If you only increase the maximum memory size, this may lead to the reduced concurrency of large queries.

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

The out of memory (701) error is a generic error returned when tasks attempt to allocate a block of memory from the memory manager and the attempt fails. For more information, see MSSQLSERVER_701.

The following conditions can cause this error:

• The memory pool reaches its total limit.
  

Note:
Resource Governor may not be the sole cause of this condition. There may be other applications running on the server that have memory demands that contribute to this condition.

• Multi-page or virtual address space allocation fails because the virtual address space does not have a free block big enough for the required reservation. This is most likely to occur on a 32-bit architecture, and unlikely to occur on a 64-bit architecture.
  
• Multi-page or virtual address space allocation fails because the total commitment reaches the commit limit. This applies to both 32-bit and 64-bit architectures.
  

When you see the out of memory error, the error log is the best starting point for investigating the error. The log contains output similar to the following example:

2006-01-28 04:27:15.43 spid51 Failed allocate pages: FAIL_PAGE_ALLOCATION 1

The possible failures recorded in the error log are:

• FAIL_PAGE_ALLOCATION, followed by the number of pages that were attempted to allocate
  
• FAIL_VIRTUAL_RESERVE, followed by the number of bytes that were attempted to reserve
  
• FAIL_VIRTUAL_COMMIT, followed by the number of bytes that were attempted to commit
  

It is important to understand that the task that triggered the out of memory error is often not the task that caused the error. Unless there is a runaway task, the out of memory condition is typically the culmination of multiple running tasks. As a result, using the very common case of a FAIL_PAGE_ALLOCATION error, your investigation must take a broader view of system activity.

The next valuable piece of information in the error log is the memory status output. Depending on the failure, you should look for single page, multi-page, virtual reserved, or committed numbers for individual memory clerks. Identifying the biggest memory consumers is a key step in continuing to investigate the error. Typically, the biggest memory consumers are of the following types:

• MEMORYCLERK_* means that the server configuration or workload requires a specific memory allocation. There are corresponding memory clerks for SQL Server components, and individual components can have several memory clerks. For more information, see sys.dm_os_memory_clerks (Transact-SQL). You can sometimes identify the workload causing the problem from the memory clerks, but it is more likely that you will have to examine the memory objects associated with the clerks to find out what caused the large amount of memory consumption.
  
• CACHESTORE_*, USERSTORE_*, OBJECTSTORE_* are the types of caches. High memory consumption by a cache may mean that:
  
  • Memory is allocated out of the cache but is not yet inserted as an entry that can be evicted. This is very similar to the MEMORYCLERK case above.
    
  • All cache entries are in use so they cannot be evicted. This can be confirmed by looking at sys.dm_os_memory_cache_counters and comparing the values of the entries_count column and entries_in_use_count column.
    
• MEMORYCLERK_SQLQERESERVATIONS shows how much memory has been reserved by the query execution (QE) to run queries with sorts/joins. An out of memory error that occurs when reservations are high usually indicates a bug in the server.
  

The memory status output in the error log will also show which memory pool is exhausted. The memory brokers for every pool show the memory distribution between stolen (compilation), cached, and reserved (granted) memory. The numbers for the three brokers correspond to the preceding memory objects associated with the memory clerks. You can find out how much memory is allocated for a pool from a given clerk or memory object by extracting the information from a full dump using a custom script and the sys.dm_os_memory_cache_entries dynamic management view, which shows the pool_id that each entry is associated with.

If you need to contact Customer Support Services, collect the following information for our support staff:

• The error log showing the out of memory error and the memory status output at the time of the error.
  
• The output from the following statements:
  
  dbcc memorystatus
  dbcc sqlperf(spinlockstats)
  select * from sys.dm_os_memory_clerks
  select * from sys.dm_os_wait_stats order by wait_type
  select * from sys.dm_os_waiting_tasks
  select * from sys.dm_os_ring_buffers where ring_buffer_type='RING_BUFFER_OOM'
  select * from sys.dm_os_ring_buffers where ring_buffer_type='RING_BUFFER_RESOURCE_MONITOR'
  select * from sys.dm_os_ring_buffers where ring_buffer_type='RING_BUFFER_MEMORY_BROKER'
  select * from sys.dm_os_memory_cache_clock_hands

• Optionally, an out of memory dump collected with T8004. This minidump will have valuable information such as the ring buffers and spinlock/wait stats. The dump counter for T8004 can be reset without restarting the server by turning T8004 off and back on.
  

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.