Resource governor

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;