Resource Governance - SQL Server

SQL Server provides Resource Governor, a feature than you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU and memory that incoming application requests can use.

In the Resource Governor context, workload is a set of similarly sized queries or requests that can, and should be, treated as a single entity. This is not a requirement, but the more uniform the resource usage pattern of a workload is, the more benefit you are likely to derive from Resource Governor. Resource limits can be reconfigured in real time with minimal impact on workloads that are executing.

The following three concepts are fundamental to understanding and using Resource Governor:

Resource pools:  Two resource pools (internal and default) are created when SQL Server 2012 is installed. Resource Governor also supports user-defined resource pools.

Workload groups: Two workload groups (internal and default) are created and mapped to their corresponding resource pools when SQL Server 2012 is installed. Resource Governor also supports user-defined workload groups.

Classification: There are internal rules that classify incoming requests and route them to a workload group. Resource Governor also supports a classifier user-defined function for implementing classification rules.

NOTE: By default the Resource Governor is disabled.

In the context of Resource Governor, you can treat the preceding concepts as components. The following illustration shows these components and their relationship with each other as they exist in the database engine environment. From a processing perspective, the simplified flow is as follows:

There is an incoming connection for a session (Session 1 of n).

• The session is classified (Classification).
• The session workload is routed to a workload group, for example, Group 4.
• The workload group uses the resource pool it is associated with, for example, Pool 2.
• The resource pool provides and limits the resources required by the application, for example, Application 3.

Enable Resource Governor

Resource Governor can be enabled in following ways:

1. T-SQL

2. Object Explorer

3. Resource Governor Properties

Using T-SQL

Run ALTER RESOURCE GOVERNOR RECONFIGURE statement to enable Resource Governor.

Example:

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

Using Object Explorer

STEP 1: SQL Server Management Studio è Open Object Explorer and recursively è Expand the Management è Resource Governor.

STEP 2: Right-click Resource Governor è Click Enable. 

Using Resource Governor Properties

STEP 1: In SQL Server Management Studio è Open Object Explorer and recursively è Expand the Management è Resource Governor.

STEP 2: Right-click Resource Governor è Click Properties, this opens the Resource Governor Properties page.

STEP 3: Check the Enable Resource Governor check box è Click OK.

Disable Resource Governor

Resource Governor can be disabled in following ways:

1. T-SQL

2. Object Explorer

3. Resource Governor Properties

Using T-SQL

Run ALTER RESOURCE GOVERNOR DISABLE statement to enable Resource Governor.

Example:

ALTER RESOURCE GOVERNOR DISABLE;
GO

Using Object Explorer

STEP 1: SQL Server Management Studio è Open Object Explorer and recursively è Expand the Management è Resource Governor.
STEP 2: Right-click Resource Governor è Click Disable.

Using Resource Governor Properties

STEP 1: In SQL Server Management Studio è Open Object Explorer and recursively è Expand the Management è Resource Governor.
STEP 2: Right-click Resource Governor è Click Properties, this opens the Resource Governor Properties page.
STEP 3: UnCheck the Enable Resource Governor check box è Click OK.

Resource Governor Resource Pool

A resource pool, or pool, represents the physical resources of the server. You can think of a pool as a virtual SQL Server instance inside of a SQL Server instance.

The following are parts for the Pool:

1. MIN, MAX or CAP for CPU

2. MIN or MAX for memory

CAP for CPU:

The CAP value for CPU represents a hard maximum. Available CPU capacity above this value will not be used.

MIN or MAX for memory:

MIN and MAX represent the minimum guaranteed resource availability of the pool and the maximum size of the pool respectively for each of these resources.

The sum of MIN values across all pools cannot exceed 100% of the server resources.

MAX and CAP values can be set anywhere in the range between MIN and 100% inclusive.

Resource Governor has two resource pools:

1. Internal pool

2. Default pool

Internal Pool:

The internal pool represents the resources consumed by the SQL Server itself. This pool always contains only the internal group, and the pool is not alterable in any way. Resource consumption by the internal pool is not restricted. Any workloads in the pool are considered critical for server function, and Resource Governor allows the internal pool to pressure other pools even if it means the violation of limits set for the other pools.

NOTE: The internal pool and internal group resource usage is not subtracted from the overall resource usage. Percentages are calculated from the overall resources available.

Default Pool:

The default pool is the first predefined user pool. Prior to any configuration the default pool only contains the default group. The default pool cannot be created or dropped but it can be customized. The default pool can contain user-defined groups in addition to the default group.

NOTE: The default group is customizable but it cannot be moved out of the default pool.

Create a Resource Pool

Resource Pool can be created in following ways:

1. T-SQL

2. Object Explorer

Using T-SQL

STEP  2: Run the ALTER RESOURCE GOVERNOR RECONFIGURE statement.

Example:
CREATE RESOURCE POOL test_Pool  WITH (MAX_CPU_PERCENT = 35);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

STEP  1: Run CREATE RESOURCE POOL statement specifying the property values to be set.

Using Object Explorer

STEP 1: In SQL Server Management Studio è Open Object Explorer and recursively è Expand the Management è Expand the Resource Governor.
STEP 2: Right-click Resource Governor è Properties.
STEP 3: In the Resource pools grid è Click the first column in the empty row. This column is labeled with an asterisk (*).
STEP 4: Double-click the empty cell in the Name column. Type in the name that you want to use for the resource pool.
STEP 5: Click or double-click any other cells in the row you want to change, and enter the new values.
STEP 6: Click OK to save the changes.

Delete a Resource Pool

Resource Pool can be deleted in following ways:
1. T-SQL
2. Object Explorer

Using T-SQL

STEP 1: Run the DROP RESOURCE POOL statement specifying the name of the resource pool to delete.

STEP 2: Run the ALTER RESOURCE GOVERNOR RECONFIGURE statement.

Example:

DROP RESOURCE POOL test_pool;
GO

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Using Object Explorer

STEP 1: In SQL Server Management Studio è Open Object Explorer and recursively è Expand the Management è Expand the Resource Governor.
STEP 2: Right-click the resource pool to be deleted è Click Delete.
STEP 3: In the Delete Object window, the resource pool is listed in the Object to be deleted list. To delete the resource pool è Click OK.