Introduction
It is 10 am, in a stressful morning, 5 weeks after deploying a management application. Access to the database becomes increasingly slow and cumbersome from this application. Simple queries that once executed in less time, now take forever to run. Users call in all directions, we are afraid that the system will fall. All the IT are on the lookout, running in all directions, surfing on Google, flipping through books, reviewing the algorithms to palliate the situation.
Developers, IT network administrators and we the database administrators accuse each other. We could not understand what was happening. DBAs believe that the application is starting to behave badly, a problem with the algorithms may be poorly implemented, or the influence of the data stored in the database. In turn, developers rejected error to network administrators, they thought the networks were not optimal, while the latter were sure of their Job.
Everyone was in total panic, amazing thing, we DBAs think to be set apart by the fact that the database should depend on the application and the quality of the link. But the reality was that we DBA were the basis and origin of what was happening without knowing it. But how ? You will find out.
The optimization of the database depended on us DBA because all the applications connecting to the data base (Main Application of Management, Reporting, Maintenance requests and scheduled tasks) executed the stored procedures created in Beforehand by DBAs or stored system procedures and stored in the database. Hence 98%, we were the cause of all that was happening.
A relational database management system (RDBMS) is like a memory: it contains important, if not vital, data for the company. And the ability of the company's stakeholders to access it efficiently and quickly with consistent response times is essential and this remains the primary responsibility of a database administrator for a data base in production. No excuse there !
Rudi BRUCHEZ, Optimisez SQL SERVER, Edition DUNOD Paris 2008, Page 10
However, the optimization of a data base remains a total business of DataBase Administrators, a data base in development phase is totally different from a data base in production.
DataBase Administrators will need to make the necessary arrangements to ensure optimization of the server as long as they:
- From the application development step, support developers with more optimal query design and data control methods. (Because sometimes a developer often considers the database as a bag that can be inserted anything, at any time and in any way. All that interests him is that the application works, he is even ready to create a table "sex" to overcome any difficulty that prevents him to finish his task quickly. lots of laughs !! ). That is why it is the responsibility of the DBA to participate in the development of the application to put things in order.
- During the deployment and production of the application implement measures to optimize the database to ensure the highest possible optimizat
Of course, the slowness of an information system is not necessarily and always linked to the configuration of your database server, there are many issues that take into account among others:
- the quality of the connection,
- the quality of source codes,
- quality and performance of computers, etc.
I have carried out extensive research on previously published articles on this subject and decided to publish at my level this article in order to share this experience that I experienced, Optimization concerns only the configuration of the server.
I think it will be useful for database administration and this is not always the only true solution to optimize our servers. But this experience can help you boost your application. I also admit that this article is not perfect, from where I remain humble and recessive to all your remarks considering the importance that it will give me even more motivation.
Governor of Resources
Good memory is the goal of each and every one of us. We always like to remember easily these past events, previous information, details of an operation, the name of a person and this in record time. Each one of us is not happy to no longer remember something, that is indisposed.
Imagine that you are listening to a song whose name the author escapes you, you are forced to think to find his name, more it takes time more bother you. Imagine that you should give the password of a server you do not remember anymore to your manager, it is obvious that it will be even more stressful to you because your manager has been standing for 2 minutes and you still think about What to tell him. It always disturbs !
Just as a relational database management system (RDBMS) is like a memory: it contains important, very vital data for the company. And the ability of the company's stakeholders to access it efficiently and quickly, with consistent response times, is essential and this remains the primary responsibility of a database administrator!
Definition
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.
We can have several queries that come from all corners to connect to the database. These queries can be classified into 3 categories:
- Main Application
Each database has always had an application for which it was created. This application runs multiple requests over the length of days depending on whether users log on to it. Therefore, these requests must be the priority among many others, because all that can be of interest to such a system is the speed and the flexibility of the application to carry out all the operations that are asked of it Display a list, delete or modify information etc.).
- Maintainance of the Server
Occasionally, server maintenance plans are planned by implementing scheduled tasks that in turn execute some scripts containing heavy maintenance, server optimization, and so on. These queries often monopolize many resources on their own to run. This makes slow execution of other queries mainly those that come from the application.
On the other hand there are applications of Monitoring servers (KANKURU for example) which every minute must give you the general state of the server. This is to help you detect and prevent breakdowns or problems. Indeed these Monitoring applications run hundreds and hundreds of requests every minute in the worry of giving you the relative information of the servers every possible minute.
These queries do not constitute a certain view priority that they are not executed from the application. Therefore the allocation of resources must be reasonable in order not to prevent or render heavy the execution of the requests coming from the application.
This is why a maximum of resources must be allocated to these requests to ensure the correct use and optimization of the application. So there is no excuse in terms of cumbersome application.
- Reporting
These queries are known to be consuming enormous resources. A Reporting can be accessed at any time by various users to have an overview on transactional operations for example.
This is why, in our case, considering to prioritize the optimization of the main application, one can tolerate that a Reporting is slow to the detriment of the application. Reporting is characterized by its heaviness, often using all the resources to execute in record time and to minimize its heaviness.
This being so, the database administrator must be able to set up a resource governor to address some difficulties such as :
- Query Control Losses on the Server: A query that consumes large resources can use most of all server resources.
- Unpredictable workload execution: Simultaneous applications running on the same server have different workloads of different sizes and types. For example, it could be two data warehouse applications or a combination of OLTP applications and data warehouses. These applications are not isolated from one another and the resulting competition between resources results in unpredictable execution of the workload.
- Definition of the priority level of a workload: a workload is allowed to be processed faster than another or its execution is guaranteed in case of resource conflicts. The Resource Governor allows you to assign a parameter of relative importance to each workload.
Imagine for us, that's all we could not implement. I promised you above that I was going to tell you the reason why the application had become cumbersome.
The application was deployed according to this architecture:
The File Server
The application stores a large number of files. To free and lighten the database we have to create a file server where the application stores all the files and referencies these files by their respective links in the database. This being the growth of the database will be managed.
The Application Server
This is the server on which the application was deployed.
The database server
This is the server on which the database was deployed.
The Reporting Server
This is the server on which the Reporting was deployed.
As a result, we have grouped 3 categories of users.
- Users who use the application, they perform multiple transactions each according to its assignments. There is a significant number of users deployed in many sites for the operation of the application.
- Those who display Reporting. There was a significant number of people who at any time logged in to view the report of all operations. These users have the ability to do this at any time and this as many times as they want, because of the importance and scope of the reports. In addition there are several types of reports. You can imagine how hundreds of heavy reporting queries carry out and come to the data base.
- In addition, the database administrators, who initiate maintainance requests at any time, implements scheduled tasks that run in the background on the server. They use a large number of deployed monitoring applications that run on multiple machines. You understand that you can never manage such a database with a single DBA. So a handful of men every hour at the bedside to guarantee the high availability of the system.
All this justifies how stressed the database server was every two minutes. As a result, it was the application that remained cumbersome and too slow. At the beginning, everything worked, but the more we evolved, the data filled the database and the system was only getting worse.
Many will probably tell me that the server hosting the DBMS was not as powerful as that, either the RAM or the processor, but as you know, even a year later you will have always seen worse.
You understand how responsible we are for this slowness. Slowness may not always be linked to the causes mentioned above. But the advice mentioned in this article can help you optimize your system, because in our case we applied these optimization methods, suddenly as with a magic wand, the application was more optimal than ever. The idea would be to optimize the application as the only important element for which the database exists. Hence the priority is to optimize the main application.
Implementation
Implementing a resource governor with SQL SERVER takes place in 4 steps:
- 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.
- Creating Users on the basis of the steering data and mapping them to the previously created connection accounts and assigning rights.
- Pooling resources for previously created connection accounts.
- Creating workload group and mapping to relative resource pools
- Creation of the function of classification and test function of the function
- Instruct the Resource Governor to use the classification function.
- Activation of the Resource Governor
We will work with a test database: db_Gouvernor
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];
CREATE LOGIN Login_Maintenance
WITH
PASSWORD=N'%123456%',
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF;
GO
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_Gouvernor
USE [master];
GO
CREATE USER User_Maintenance FROM LOGIN Login_Maintenance;
GO
GRANT EXECUTE ON DATABASE::db_Gouvernor TO User_Maintenance;
GO
sp_defaultdb 'User_Maintenance','db_Gouvernor'
GO
CREATE USER User_Reporting FROM LOGIN Login_Reporting;
GO
GRANT EXECUTE ON DATABASE::db_Gouvernor TO User_Reporting;
GO
sp_defaultdb 'User_Reporting','db_Gouvernor'
GO
STEP 3: Creation of the 2 ressources
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
CREATE RESOURCE POOL Pool_Maintenance
WITH
(
min_cpu_percent=0,
max_cpu_percent=20,
min_memory_percent=0,
max_memory_percent=40
)
GO
CREATE RESOURCE POOL Pool_Reporting
WITH
(
min_cpu_percent=0,
max_cpu_percent=20,
min_memory_percent=0,
max_memory_percent=40
)
GO
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 workload 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 workgroup 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 10
USE [master];
GO
CREATE WORKLOAD GROUP Group_Maintenance
USING Pool_Maintenance;
GO
ALTER WORKLOAD GROUP Group_Maintenance
WITH
(
importance =LOW
)
GO
CREATE WORKLOAD GROUP Group_Maintenance
USING Pool_Maintenance;
GO
ALTER WORKLOAD GROUP Group_Maintenance
WITH
(
importance =LOW
)
GO
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 Ressource Governor to use the classification function
USE [master];
GO
ALTER RESOURCE GOVERNOR
WITH
(
CLASSIFIER_FUNCTION = dbo.Function_Classification
);
STEP 7: Enable the Ressource 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. Verfication 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 Ressource 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. Desable Ressource Governor
ALTER RESOURCE GOVERNOR DISABLE;
GO
