How to Manage Database Resources in OutSystems: SQL Server Resource Governor
One of the most significant difficulties of database performance tuning is trying to manage resources with competing workloads on a shared database server. The number and type of applications that are running on the database determine the server workload and the amount of consumed system resources.
If one or several applications exceed the workload threshold, the server can become destabilized, preventing critical background processes from running on time. The response time can become prolonged, making it difficult to debug the source of the excessive load or to log into the administration console to identify and fix the problem.
To prevent this kind of issues and to manage the database server workload, some vendors have equipped their database engines with resource management features. In this article, we will focus on Resource Governor, SQL Server Enterprise, showing you how to use it to enable workload isolation. Because this is a database-specific feature, any OutSystems application can take advantage of it, and no special configuration is needed.
How Does Resource Governor Work?
Resource Governor is available in SQL Server Enterprise. It enables the management of SQL Server workloads by specifying limits of resource consumption from incoming requests. At the time of writing, Resource Governor was not available in Amazon RDS for SQL Server or Azure SQL as Database as a Service (DBaaS) for cloud offers. However, it is available in managed offers like Azure SQL Database Managed Instance. Resource Governor has three main concepts:
- Resource pools: represent the physical resources of the server. Resource Governor allows the user to specify limits on the amount of CPU, memory, and physical input/output (I/O) that incoming application requests can use. When SQL Server is installed two resource pools (internal pool and default pool) are created, and the user can add more resource pools.
- Workload groups: act as containers for session requests with similar classification criteria, and are located inside a resource pool. When SQL Server is installed, two workload groups (internal group and external group) are created and mapped to the corresponding resource pools. The user can add more workload groups.
- Classifier Function: assigns incoming sessions to a workload group, according to their classification. The user customizes the classifier function, to classify and assign the incoming sessions, and the overall database usage according to the application requirements.
The following image summarizes these concepts. When an application connects to the database engine, it is classified and then distributed to a workload group. One or more workload groups are then assigned to specific resource pools.
Getting Started With Resource Governor
Let’s consider the following scenario and its resource management requirements. A company called ABC Corp. runs two OutSystems applications (Salary Processor and Invoicer), both are critical to its business, and they are both connected to an SQL Server database:
- Salary Processor: this is a Business Processing Management (BPM) based application, highly transactional and CPU intensive, that runs once per month during the daytime;
- Invoicer: a web application used in the ABC stores, it issues all the invoices to the customers.
- Service Center: OutSystems Service Center, the environment management console.
The database resource manager (Resource Governor) segregates user access to the Invoicer from the Salary Processor’s asynchronous processes and timers, shielding the end-user quality of service (QoS) from any abnormal load in asynchronous jobs. Service Center manages and monitors the OutSystems platform at all times and is immune to unexpected peak workloads of other applications.
Step 1: Create Resource Pools
Resource pools impose limits on the amount of CPU that incoming application requests can use within the resource pool. For our scenario, we will need to create two resource pools:
CREATE RESOURCE POOL rp_salary_processor
WITH (
MIN_CPU_PERCENT = 0,
MAX_CPU_PERCENT = 10,
MIN_MEMORY_PERCENT = 0,
MAX_MEMORY_PERCENT = 100
);
GO
CREATE RESOURCE POOL rp_service_center
WITH (
MIN_CPU_PERCENT = 50,
MAX_CPU_PERCENT = 100,
MIN_MEMORY_PERCENT = 0,
MAX_MEMORY_PERCENT = 100
);
GO
- rp_salary_processor: used by the Salary Processor. In a scenario with CPU contention, the Salary Processor is allocated a maximum of 10% CPU usage. The resource pool enables the database server to reserve enough available resources for other applications. In this scenario, the Salary Processor queries may become slower and the overall processing may take longer. However, it shouldn’t be problematic since this type of batch processing isn’t typically time-sensitive.
- rp_service_center: used by Service Center. In a scenario with CPU contention, Service Center is allocated a minimum of 50% CPU, ensuring our access to Service Center, even when the database server is with high CPU usage.
Step 2: Create Workload Groups
The second step is to create the workload groups, which we then associate to the corresponding resource pools:
CREATE WORKLOAD GROUP grp_salary_processor
USING "rp_salary_processor";
CREATE WORKLOAD GROUP grp_service_center
USING "rp_service_center";
Step 3: Create and Define the Classifier Function
The next step is to create a classifier function that maps incoming session requests to the appropriate workload groups. To segregate the workload, a proper classifier function must be defined, and this depends on how the applications are using the database.
Define the Classifier Function for a Shared Database Scenario
In this scenario, the OutSystems platform runs on its own server and each application runs in a separate front-end server, they all share the same database. To use the Resource Governor when all the applications share the same database, the classifier function should assign each application to a different front-end server, as demonstrated in the following figure.
In this scenario, the selection criteria of the classifier function are the front-end servers from where the session requests arrived:
CREATE FUNCTION fnClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @val sysname
SET @val='default'
IF HOST_NAME() = 'Frontend1'
SET @val = 'grp_salary_processor'
IF HOST_NAME() = 'PlatformServer'
SET @val = 'grp_service_center'
RETURN @val
END;
GO
Define the Classifier Function for Multiple Databases Scenario
In this scenario, the selection criteria of the classifier function are the database names:
CREATE FUNCTION fnClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @val sysname
SET @val='default' IF ORIGINAL_DB_NAME() = 'SalaryDB'
SET @val = 'grp_salary_processor'
IF ORIGINAL_DB_NAME() = 'PlatformDB'
SET @val = 'grp_service_center' RETURN @val
END;
GO
Bear in mind that both classifier functions return a default workload group, and these will be assigned to the Invoice application and to all other non-classified sessions.
To finish creating the classifier function, we need to register it and update the in-memory configuration:
ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.fnClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
Step 4: Validate the Resource Pools and Workload Groups
We can now verify the resource pools and workload groups are properly configured by running these queries:
SELECT * FROM sys.resource_governor_resource_pools;
SELECT * FROM sys.resource_governor_workload_groups;
GO
To check if the classifier function exists and is enabled, run the following queries:
SELECT * FROM sys.resource_governor_configuration;
GO
SELECT
object_schema_name(classifier_function_id) AS [schema_name],
object_name(classifier_function_id) AS [function_name]
FROM sys.dm_resource_governor_configuration;
Database resource management benefits end-user QoS, shielding them from experiencing unresponsive or excessively slow applications. Resource Governor enables you to manage your available resources by implementing workload isolation, and as you have seen, it’s pretty straightforward.
