The Resource Governor is a technology introduced by SQL Server 2008 to manage workload and SQL Server resources by specifying limits on resource consumption by incoming requests. From the resource governor’s point of view, the workload is a set of queries of similar size that can or should be treated as a single entity. While this is not necessary, the more consistent the use pattern of a workload resource, the more beneficial it is to use the Resource Governor. Resource limits can be reconfigured in real time with negligible impact on workloads running.
The Resource Governor is available only in the Enterprise, Developer, and Evaluation editions of SQL Server.
A database administrator, for the needs of an enterprise, must set up the resource governor this will allow the limit of resources allocated to certain types of requests executed on the production database.
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
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.
David BARBARIN , Introduction au Gouverneur des Ressources avec SQL SERVER 2008 , Developpez.com, 2009 , Page 5
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).
David BARBARIN , Introduction au Gouverneur des Ressources avec SQL SERVER 2008 , Developpez.com, 2009 , Page 1
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
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
This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)