Resource Governor Configuration

Resource Governor(RG) is a technology which introduced in SQL server 2008 to manage the CPU and Memory resource that a incoming session can use.This feature is only available on Enterprise edition ,Develepoer edition and Evaluation Edition.

RG can be configured to restrict the resource utilization of user session. You can use it not only to limit the maximum usage , but also to guarantee minimum resourse for each connection. This can be implimented in many scenario like server consolidation where multiple application datbases are hosted on same instance of SQL server. As a DBA it is our responsibilty to guarantee minimum resource for connection coming from each application. RG will help you to slice the resource to each application based on the demand.

Below are three terminology which required  to understand the RG

Resource Pool : You can see resource pool as slice of SQL instance's CPU and Memory. It is like dividing the CPU power and Memory into multiple pool. A resource pool can be defined by specifying the Min CPU percentage ,Max Cpu Percentage,Min Memory Percentage and Max memory percentage. By default the min and max is defined as 0 and 100. By default there are two resource pools : Default and Internal. Internal Pool is reserved for SQL sever internal process. Internal Pool has precedence over other pool which can override setting of any other pool. we can define 18 user defined pool which make total of 20 resource pool. Let us assume that we have hosted the databases of three application (App1 ,App2,App3) in the same instance of the SQL server and we have to give minimum of  35% ,40%   and 10% of resource to App1 ,App2  and App3 respectively.Let us defined three Resource pool RP1,RP2 and RP3 for App1,App2,App3 respectivly.

The thumb rule is sum of Min% of all pool should not exceed the 100. In a nutshell, at least under heavy load, the effective maximum percentage of a resource that a specific pool can consume is the maximum for that pool less the sum of the minimum resources defined for all of the other pools, and the shared percentage is calculated as the effective maximum less the minimum. The T-Sql  for defining the pool is given below

Workload Group: Workload groups are defined under the resource pool and incoming sessions are routed to the workload group which in turn use the underlying resource pool settings. With resource pool we can control the CPU and Memory but workload group below the resource pool will help us to add additional controls for different sets of users across the same resource pool. Let us assume that for one application APP2, there are two types of users OLTP users and Report Users. We can define more precedence to OLTP users over Report user under the same resource pool RP2 using workload group. As we have system defined resource pool, there are two internal workload group, Internal and default. A work load group can be defined with below parameters.

• IMPORTANCE: Importance is not really the priority. it is relative importance and  allow the scheduler to balance the work for individual request in the workload group.This settings is only effective with in all the workload group in a specific resource pool. Possible values for this parameter are LOW,MEDIUM and HIGH . MEDIUM is the default value.

• Max_DOP: It define the max degree of parallelism allowed for any query running under this workload group.The default for this is 0 which in turn take the global setting defined by Sp_Configure.When this value is non-zero, the scheduler will prevent a parallel plan from exceeding this number of parallel threads when it serve the request

• REQUEST_MAX_MEMORY_GRANT_PERCENT: This will define the percentage of of total memory available in the underlying resource pool that can be granted to any request in the workload group. The default value is 25. If a request requires more memory than the defined one, for the default workload group (system defined)  the server grant the memory as far as it is available in the pool .Otherwise it will throw time out error with error number 8645. For used defined workload group, the server will try to lowering the DOP until the memory limit is satisfied.if the memory requirement is still not matching or parallelism is already 1 , then system will throw an error 8657.Increasing the value of this parameter allows a single query to consume larger amount of memory, and reducing it allows more memory-bound queries to run concurrently.

• REQUEST_MEMORY_GRANT_TIMEOUT_SEC: This setting allows us to specify the number of seconds that a query can wait for a memory grant. The default is 0 and it means that the wait time will be determined based on the cost of the query. If the time exceed the setting system will throw an error 8651

• GROUP_MAX_REQUEST: This setting allows us to specify the number of concurrent requests that can be run at any time with in the workload group. The default is 0 which means no limit.

• REQUEST_MAX_CPU_TIME_SEC: This setting allows us to specify the CPU time any single query within the group can take before a CPU threshold exceeded event is raised.Unlike the query governor cost limit (sp_configure) ,which refuses to run the query which exceed the estimated cost , this setting does not prevent query from running the query.Instead, the trace event is raised at the first time a query is exceed the limit.The default value is 0 , which means there is no limit and no trace event will be raised

The T-SQL for define the workload group

 

Classifier Function: The classifier function is a user defined function which resides in the Master database and helps to route the user request to appropriate workload group and in turn to the underlying resource pool and it return the workload group name.While establishing the connection, the classifier function come into picture after the authentication process and log on triggers.There are several method to determine the the workload group of a incoming request.It can be done using the log in account,windows group,server role,application name,host name,time of the day, day of the week or month or your own custom classification logic using the LOGIN_NAME(),HOST_NAME(),APP_NAME(), CONNECTIONPROPERTY(), LOGINPROPERTY(),IS_MEMBER(),DATEPART/() etc.While writing the classifier function we should be very careful to make it very accurate and efficient as possible. This function has the potential to be the the biggest bottleneck on your system, because it is executed for every new connection to the server.

Below is the Tsql to define the classifier function

Below flowchart will give better understanding of the resource governor. Red color blocks are internal pool/group

Finally to enable the Resource governor

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.ResourceClassifier);     

GO      

ALTER RESOURCE GOVERNOR RECONFIGURE

Associated dynamic view:

• sys.dm_resource_governor_workload_groups
• sys.dm_resource_governor_resource_pools

By joining sys.dm_exec_sessions with  sys.dm_resource_governor_workload_groups on group_id column, we can identify the session workload group

Limitations of Resource Governor:

The Resource Governor administrates how much memory is assigned to a query at runtime, and NOT how much memory is used by the query’s data. In other words, a query which needs a small amount of memory to compile and run might bring tons of data into memory, and the Resource Governor cannot do anything about it. In short resource governor has no control over the Buffer Pool. It can control only the Working Memory for the query, memory used for sorting,locks,hashing etc. 

Resource governor does not have any control over the I/O system.In many of the system the I/O can often be the most prevalent bottleneck.The current implementation of resource governor does not provide control for I/O activities