RSS Feeds

Article Topics

All Articles

Performance Tuning

Audit

Business Intelligence

Clustering

Reporting Services

SQL Azure

Developer

General DBA

ASP.NET / ADO.NET

SQL Azure

USEFUL SITES :

ASP.NET Tutorials
Windows and SQL Azure Tutorials
Cloud Hosting Magazine
SharePoint Tutorials
Windows Server Help

Write for Us

Share your SQL Server knowledge with others and raise your profile in the community More...

Resource Governor in SQL Server 2008

By : Ashish Kumar Mehta
Aug 08, 2008

SQL Server 2008 introduces a new feature called Resource Governor which enables Database Administrators to manage SQL Server workload and critical system resource consumption. Resource Governor enables DBA to specify limits on the amount of CPU and memory which the incoming sessions to the SQL Server can use. In a production environment DBA's will come across scenarios when there could be sudden spike in CPU and memory utilization thereby resulting in slow responses to query requests. These issues happen when there is unpredicted workload execution like long running TSQL queries, database backups being performed etc. The solution to these issues is the use of Resource Governor which enables DBA's to differentiate workloads and allocate shared resources as they are requested, based on limits you specify for resources like CPU and memory. The resource governor limits can easily be reconfigured in real time with minimal impact on the workloads that are executing.

Resource Governor Concepts
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

Next Page>>