Control your server resources Using SQL Server

Implementing a resource governor with SQL SERVER takes place in 4 steps:

1. Creation of the 2 connection accounts which corresponds to the 3 types of source of the requests that can connect to the base server of the data.

2. Creating Users on the basis of the steering data and mapping them to the previously created connection accounts and assigning rights.

3. Pooling resources for previously created connection accounts.

4. Creating workload group and mapping to relative resource pools

5. Creation of the function of classification and test function of the function

6. Instruct the Resource Governor to use the classification function.

7. Activation of the Resource Governor

We will work with a test database:  db_Governor

IMPLEMENTATION

STEP 1  : Creating the 2 connection accounts (Logins)

As mentioned above, each category of requests will have its login account. As the application is more privileged, we will just create the two connections for the other types of queries (Maintenance and Reporting).

 

USE [master]; 

--  Login for Maintenance requests--

CREATE LOGIN Login_Maintenance
WITH
PASSWORD=N'%123456%',
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF;
GO
 

-- Login for Reporting  requests--

CREATE LOGIN Login_Reporting
WITH
PASSWORD=N'%123456%',
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF;
GO

You will tell me but why not an account or login for the users of the application? We chose to use the default account.

 

STEP 2  : Creation of the 2 users on the Database to gouvern

This automatically constitutes a mapping of these users to their respective login. We assume that our steering database already exists under the name:db_Governor

USE [master]; 
GO

-- User for Maintenance requests--
 
CREATE USER User_Maintenance FROM LOGIN Login_Maintenance;
GO
GRANT EXECUTE ON DATABASE::db_Governor TO User_Maintenance;
GO
sp_defaultdb 'User_Maintenance','db_Governor'
GO

-- User for Reporting requests

CREATE USER User_Reporting FROM LOGIN Login_Reporting;
GO
GRANT EXECUTE ON DATABASE::db_Governor TO User_Reporting;
GO
sp_defaultdb 'User_Reporting','db_Governor'
GO

 

STEP 3  : Creation of the 2 resources

A resource pool allows you to set limits or impose constraints on the use of server resources. The resources involved are processors and memory. It is not possible to perform the same input / output control of the disk subsystem at this time. The sizing of resource pools and workload groups is an important part of the process of setting up the resource governor. The challenge here is to determine the resources needed to allocate according to business needs. Of course, it is possible to modify any parameter of a resource pool or a workload group a posteriori. As we have seen, a resource pool has two distinct parts, part of which is shared with the other pools and another fixed part reserved for it exclusively. The values ​​MIN and MAX are important here because they make it possible to calculate the shared resources of a pool on the one hand and the maximum of effective resources that can use the same pool on the other hand.

USE [master]; 
GO

-- Resource Pool for Maintenance Requests 
 
CREATE RESOURCE POOL Pool_Maintenance
WITH
(
    min_cpu_percent=0, 
    max_cpu_percent=20, 
    min_memory_percent=0, 
    max_memory_percent=40 

)
GO

-- Resource Pool for Reporting Requests 

CREATE RESOURCE POOL  Pool_Reporting
WITH
(
   min_cpu_percent=0,
   max_cpu_percent=20,
   min_memory_percent=0,
   max_memory_percent=40
) 
GO

-- Update Default Pool for Application Requests

ALTER RESOURCE POOL [default]
WITH
(
    min_cpu_percent=50,
    max_cpu_percent=100,
    min_memory_percent=50,
    max_memory_percent=100
)

GO

MIN_CPU_PERCENT: This option allows a CPU contention to guarantee a minimum percentage of resources for active queries in a pool. It is important to understand this. The Resource Governor balances the available resources for all active threads within that pool. The values ​​of the parameter MIN_CPU_PERCENT can be between 0 and 100, but the sum of the values ​​of all pools of the resource governor can not be greater than 100.

MAX_CPU_PERCENT: When a CPU contention exists, this parameter ensures that all active threads in the pool do not consume resources beyond a certain threshold. This parameter can not be set to 0. The value of the MAX_CPU_PERCENT parameter must be greater than or equal to the value of the parameter MIN_CPU_PERCENT.

MIN_MEMORY_PERCENT: This parameter ensures a certain percentage of available memory resources for a pool in all cases. In other words, even if there is no memory contention, a quantity of memory will still be reserved for the use of this pool even if no activity is present in the pool. Incorrect settings would result in reserving the memory resource unnecessarily while other pools might need it. In the same way as the parameter MIN_CPU_PERCENT, the values ​​can be between 0 and 100. The sum of the values ​​of all pools for this parameter can not exceed 100%.

MAX_MEMORY_PERCENT: This parameter ensures that the memory resources consumed by all active threads in the pool do not exceed the configured threshold. The values ​​can be between 0 and 100. The value of this parameter must be greater than or equal to the value of the parameter MIN_MEMORY_PERCENT. It is important to specify here that only the memory concerning the execution cache is concerned.

 

STEP 4  : Creation of the 2 work load Groups

A workload group is a logical container that gathers incoming requests that have the same characteristics. The advantage of working groups is twofold: they allow the same resource allocation strategy to be established for all the requests of the same group and to analyze their consumption.

Two work group groups exist by default: the default group and the internal group. The default group pools all incoming requests for which there is no ranking criterion or the request routing group is missing. In general, failure to classify a request results in an automatic transfer of the request to the default group. Each workload group is associated with a notion of importance that can be set to LOW, MEDIUM or HIGH. The importance defines an available CPU resource ratio for a workload group relative to other groups within the same resource pool. This notion of importance is different from the concept of classical priority that we know. Indeed, the scheduler will distribute the different tasks of execution of the workload groups according to this ratio. Each importance corresponds to a numerical value (1 for LOW, 3 for MEDIUM and 9 for HIGH).

USE [master]; 
GO

-- Maintenance Requests Work Load Group 
 
CREATE WORKLOAD GROUP Group_Maintenance
USING Pool_Maintenance;
GO

ALTER WORKLOAD GROUP Group_Maintenance
WITH
(
    importance =LOW 

)
GO

-- Maintenance Requests Work Load Group   

CREATE WORKLOAD GROUP Group_Maintenance
USING Pool_Maintenance;
GO

ALTER WORKLOAD GROUP Group_Maintenance 
WITH 
( 
   importance =LOW 
) 
 
GO

-- Application Requests Work Load Group  

ALTER WORKLOAD GROUP [default]
WITH
(
    
    importance =LOW

)

GO

 

STEP 5  : Creation of the classification function

It is a specific user function used by the resource governor, and rules can be created that classify incoming requests and then route them to the corresponding workload groups. There are also internal rules used by the SQL engine that perform the same actions to the internal workload group.

USE [master]; 
GO

CREATE FUNCTION dbo.Function_Classification()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
RETURN
(
    SELECT CASE SUSER_NAME()
    WHEN 'User_Maintenance' THEN 'Group_Maintenance'
    WHEN 'User_Reporting' THEN 'Group_Reporting'
    ELSE 'Default'
    END
)
END

 

STEP 6  : Instruct the Resource Governor to use the classification function

USE [master]; 
GO
   
ALTER RESOURCE GOVERNOR
WITH
(
  CLASSIFIER_FUNCTION = dbo.Function_Classification
);

 

STEP 7  : Enable the Resource Governor

USE [master]; 
GO
   
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

 

Some Script

 1. check the number of queued requests in this Load group

SELECT name, total_request_count, total_queued_request_count 
FROM sys.dm_resource_governor_workload_groups

 

 2. Verification of load groups by session

EXECUTE AS LOGIN ='sa'
SELECT SUSER_NAME() AS session_name,dbo.Function_Classification()AS groupe_name;
       
EXECUTE AS LOGIN ='Login_Maintenance'
SELECT SUSER_NAME() AS session_name,dbo.Function_Classification()AS groupe_name;
       
EXECUTE AS LOGIN ='Login_Reporting'
SELECT SUSER_NAME() AS session_name,dbo.Function_Classification()AS groupe_name;

 

 3. Distribution of requests in the different pools and groups workload

 SELECT
        p.name AS pool_name,
        g.name AS group_name,
        r.session_id,
        t.text AS sql_txt
        FROM sys.resource_governor_resource_pools AS p
        INNER JOIN sys.resource_governor_workload_groups AS g
        ON p.pool_id = g.pool_id
        INNER JOIN sys.dm_exec_requests AS r
        ON r.group_id = g.group_id
        CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
        WHERE p.name <> 'internal'
        ORDER BY p.name, g.name;

4. Viewing the Resource Governor Configuration

SELECT
        p.name AS pool_name,
        p.min_cpu_percent AS pool_min_cpu_percent,
        p.max_cpu_percent AS pool_max_cpu_percent,
        g.name AS group_name,
        g.importance AS group_importance
        FROM sys.resource_governor_resource_pools AS p
        INNER JOIN sys.resource_governor_workload_groups AS g
        ON p.pool_id = g.pool_id;
        GO


SELECT * FROM sys.resource_governor_configuration;

5. Number of Requests queued

SELECT
        name,
        total_request_count,
        total_queued_request_count
        FROM sys.dm_resource_governor_workload_groups
        GO

6. Disable  Resource Governor

ALTER RESOURCE GOVERNOR DISABLE;
GO