blog

Control your server resources Using SQL Server

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.

https://technet.microsoft.com/fr-fr/library/bb895232(v=sql.105).aspx

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

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 po