Resource Governor

1. What is Resource governor?

A:

Resource Governor is a feature which can manage SQL Server Workload and System Resource Consumption. We can limit the amount of CPU and memory consumption by limiting /governing /throttling on the SQL Server.

2. Why is resource governor required?

A:

If there are different workloads running on SQL Server and each of the workload needs different resources or when workloads are competing for resources with each other and affecting the performance of the whole server resource governor is a very important task.

3. Does SQL Server have any default resource governor component?

A:

Yes, SQL Server have two by default created resource governor component.

1) Internal –This is used by database engine exclusives and user have no control.

2) Default – This is used by all the workloads which are not assigned to any other group.

4. What are the major components of the resource governor?

A:

• Resource Pools
• Workload Groups
• Classification

In simple words here is what the process of resource governor is.

• Create resource pool
• Create a workload group
• Create classification function based on the criteria specified
• Enable Resource Governor with classification function

Let me further explain you the same with graphical image.

5. Use Resource Governor to Limit CPU Usage

 

Configuring Resource Governor to Limit CPU Usage

Ensure that Resource Governor is enabled

In this Resource Governor scenario, configuration comprises the following basic steps:

Create and configure a Resource Governor Resource pool that limits the maximum average CPU bandwidth that will be given to requests in the resource pool when CPU contention occurs.

Create and configure a Resource Governor Workload group that uses this pool.

Create a classifier function, which is a user-defined function (UDF) whose return values are used by Resource Governor for classifying sessions so that they are routed to the appropriate workload group.

Register the classifier function with Resource Governor.

Apply the changes to the Resource Governor in-memory configuration.

-----

To configure Resource Governor for limiting CPU usage (Transact-SQL)

Issue a CREATE RESOURCE POOL statement to create a resource pool. The example for this procedure uses the following syntax:

CREATE RESOURCE POOL pool_name WITH (MAX_CPU_PERCENT = value);

Value is an integer from 1 to 100 that indicates the percentage of maximum average CPU bandwidth. The appropriate value depends on your environment. For the purpose of illustration, the example in this topic uses 20% percent (MAX_CPU_PERCENT = 20.)

Issue a CREATE WORKLOAD GROUP statement to create a workload group for low-priority operations whose CPU usage you want to govern. The example for this procedure uses the following syntax:

CREATE WORKLOAD GROUP group_name USING pool_name;

Issue a CREATE FUNCTION statement to create a classifier function that maps the workload group created in the preceding step to the user of the low-priority login. The example for this procedure uses the following syntax:

CREATE FUNCTION [schema_name.]function_name () RETURNS sysname

WITH SCHEMABINDING

AS

BEGIN

DECLARE @workload_group_name AS sysname

IF (SUSER_NAME() = 'user_of_low_priority_login')

SET @workload_group_name = 'workload_group_name'

RETURN @workload_group_name

END

For information about the components of this CREATE FUNCTION statement, see:

DECLARE @local_variable (Transact-SQL)

SUSER_SNAME (Transact-SQL)

Important note Important

SUSER_NAME is just one of several system functions that can be used in a classifier function. For more information, see Create and Test a Classifier User-Defined Function.

SET @local_variable (Transact-SQL) .

Issue an ALTER RESOURCE GOVERNOR statement to register the classifier function with Resource Governor. The example for this procedure uses the following syntax:

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = schema_name.function_name);

Issue a second ALTER RESOURCE GOVERNOR statement to apply the changes to the Resource Governor in-memory configuration, as follows:

ALTER RESOURCE GOVERNOR RECONFIGURE;