Resource Governor in SQL Server 2008

The following three concepts are fundamental to the understanding and usage of Resource Governor.

Resource Pool: – A resource pool represents the physical resources of the server. There are two resource pool namely internal and default which are created when SQL Server 2008 is installed. However, SQL Server also supports the creation of user defined resource pools. In a resource pool a DBA can specify MIN or MAX value in percentages for CPU and Memory utilization. The Internal pool basically represents the resources which are consumed by SQL Server itself for its running. This pool cannot be altered by a user in any way. The default pool is a predefined user pool which contains the default group. The important thing to note is that the default pool cannot be dropped or created, however it can be altered as required. Workload Group: – A workload group acts as a container which accepts the sessions from SQL server users, which are similar in nature based on the classification criteria that are applied to each requests. As in Resource Pool’s there are two predefined workload groups namely internal and default defined by SQL Server. The users cannot alter the internal workload group but can monitor it to see how SQL Server is utilizing memory and CPU. The incoming requests to the server are classified into default workload when there is no criteria defined to classify the incoming request, or there was an attempt made to classify the requests into a nonexistent workload group or there is a failure with the classification Classification: – Classifications are internal rules that classify the incoming requests and route then to a workload group. This classification is based on a set of user written criteria contained in a scalar function which will be created in the Master database. Once a DBA enables the Resource Governor on SQL Server then each and every single session to the server will be evaluated by the user defined classifier function. How to Enable & Disable Resource Governor using SQL Server Management Studio
Resource Governor is by default disabled when SQL Server 2008 is installed. To enable it using SQL Server Management Studio you need to follow the below steps. 1. Connect to SQL Server 2008 Instance using SQL Server Management Studio
2. In the Object Explorer, expand the Management Node
3. Right click Resource Governor, and then click Enable 4. Execute the following TSQL query to verify whether the Resource Governor is enabled: SELECT * FROM sys.resource_governor_configuration If the value is 1 for is_enabled then it means that the Resource Governors setting are set to true 5. Execute the following TSQL query to confirm that there is no pending reconfiguration of the resource governor because of changes in configuration, and the Resource Governor’s configuration metadata matches it’s in memory configuration. The expected value for is_reconfiguration_pending should be 0. SELECT * FROM sys.dm_resource_governor_configuration 6. To disable the Resource Governor once enabled, you need to expand the Management Node in the object explorer. Right click Resource Governor, and then click Disable
//break
How to Enable & Disable Resource Governor using TSQL
1. Execute the below TSQL query to enable Resource Governor Use Master
Go
ALTER RESOURCE GOVERNOR RECONFIGURE
Go 2. Execute the below TSQL query to disable Resource Governor Use Master
Go
ALTER RESOURCE GOVERNOR DISABLE;
Go How to create Resource Pool & Workload Group using SQL Server Management Studio
1. In the Object Explorer, expand the Management Node
2. Right click Resource Governor, and then click New Resource Pool, This will open the Resource Governor Properties page. 3. In the Resource pools grid, double click the first column and provide the values as shown in the snippet for Name, Minimum CPU %, Maximum CPU %, Minimum Memory % and Maximum Memory % 4. In the Workload groups for resource pool for PoolAdhoc, double the first column and provide the value values as shown in the snippet for Name, importance, Maximum Requests, CPU Time (sec), Memory Grant %, Grant Time-out (sec) and Degree of Parallelism. 5. A DBA also needs to alter the default resource pool with the values as shown in the snippet and then click OK to save the changes. 6. Now let’s create one more Resource Pool named PoolAdmin and a Workload Group named GroupAdmin with the values as shown in the below snippet and then click OK to save the changes. Create a Classification Function
The below TSQL query needs to be executed on the Master database to create a classification function.  Use Master
Go
CREATE FUNCTION ClassifierResources ()
RETURNS SYSNAME WITH SCHEMABINDING
BEGIN
 DECLARE @VAL VARCHAR(32)
 SET @VAL = ‘default’;
 IF ‘UserAdhoc’ = SUSER_SNAME()
  SET @VAL = ‘GroupAdhoc’;
 ELSE IF ‘UserAdmin’ = SUSER_SNAME()
  SET @VAL = ‘GroupAdmin’;
 RETURN @VAL;
END
GO The next step will be to make the function known to the Resource Governor, for that you need to execute the below TSQL statement: Use Master
Go
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = DBO.ClassifierResources)
GO In order to make the Resource Governor changes effective, you need to execute the TSQL statement below: Use Master
Go
ALTER RESOURCE GOVERNOR RECONFIGURE
GO