Separate query pool

Introduction

Some heavy database requests (DB) can fully occupy database server resources for a long time and thus make it difficult or impossible to work for other users. Among these requests are:

• Incomplete queries in dynamic groups, dashboard blocks.
• Complex analytical samples in dashboard blocks.

To solve this problem it is necessary to limit the resources allocated by the database server for processing Select-requests, or to transfer them to a separate query pool. This will reduce their impact on the work of other users and parts of the system.

Separate query pool implementation

MS SQL Server enables you to limit the allocated resources using the built-in Resource Governor tool. However, its ranking capabilities are based on information about the connection, and not a specific request. Creatio uses connections from a single pool for all queries, and since all connections are the same they are not available for ranking.

To separate the light and potentially heavy queries, the ability to send requests through a special connection in which the suffix "_Limited" is appended to the App (or Application Name) property of the connection string is added.

For example, specifying the “App = сreatio” property in the connection string of the ConnectionStrings.config file will result in it being changed to “сreatio_Limited” in the separate query pool connection. If the App (or Application Name) property is not specified in ConnectionStrings.config, the followinf default value is set for the shared connection: ".Net SqlClient DataProvider", and ".Net SqlClient DataProvider_Limited” in the separate query pool connection.

An example of connection string configuration with the App user property:

<add name="db" connectionString="App=сreatio; Data Source=dbserver\mssql2016; Initial Catalog=CreatioSolution; Persist Security Info=True; MultipleActiveResultSets=True; Integrated Security=SSPI; Pooling = true; Max Pool Size = 100; Async = true; Connection Timeout=500" />

Thus, when loading dashboards or filtering sections with dynamic groups, the application creates additional database connections that differ from the basic “_Limited” suffix.

Separating the pools will allow database administrators to regulate the allocation of resources to requests from the marked connection.

Enabling the separate query pool functionality

To enable the separate query pool functionality, set the true value for the UseQueryKinds setting in the application’s .\Terrasoft.WebApp\Web.config file.

<add key="UseQueryKinds" value="true" />

As a result, requests from dashboards and dynamic groups will be sent to connections marked with the “_Limited” suffix.

Resource Governor configuration example

Group and pool configuration is performed using an SQL-script. For example:

ALTER RESOURCE POOL poolLimited
WITH
(
    MAX_CPU_PERCENT = 20,
    MIN_CPU_PERCENT = 0
    -- REQUEST_MAX_MEMORY_GRANT_PERCENT = value
    -- REQUEST_MAX_CPU_TIME_SEC = value
    -- REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value
    -- MAX_DOP = value
    -- GROUP_MAX_REQUESTS = value
);
GO
--- Create a workload group for off-hours processing
--- and configure the relative importance.
CREATE WORKLOAD GROUP groupLimited
WITH
(
    IMPORTANCE = LOW
)
USING poolLimited
GO
ALTER RESOURCE GOVERNOR RECONFIGURE; 
GO

Learn more about the configuration in the Resource Governor documentation.

For each new connection, the classifier function is used, which returns the name of the group. For example:

USE [master]
GO
 
ALTER FUNCTION [dbo].[fnProtoClassifier]()
    RETURNS sysname
    WITH SCHEMABINDING
AS
BEGIN
    IF(app_name() like '%_Limited')
        BEGIN
        RETURN N'groupLimited'
    END
RETURN N'default'
END;

Use case

To execute a query in a separate query pool, get a special DBExecutor for it, passing the value of Limited from the QueryKind enumerator as an optional parameter. Learn more about the DBExecutor in the corresponding Multithreading when working with the database. Using the DBExecutor. In the following example, QueryKind is the argument of the EnsureDBConnection() method, the value of which comes in the custom EntitySchemaQuery request (ESQ-request), and is set to the server ESQ-request and then to the Select-request.

using (DBExecutor executor = userConnection.EnsureDBConnection(QueryKind)) {
   // ...
};

Calling EnsureDBConnection(QueryKind.General) is equivalent to calling EnsureDBConnection() without QueryKind.

Thus, if you set the instance of the Terrasoft.EntitySchemaQuery class to the QueryKind.Limited attribute in the client application, this value will be passed to the server and a special DBExecutor (using the marked connection to the database) will be provided.

An example of setting the QueryKind.Limited characteristic of a client ESQ-request in the ChartModule schema:

...
getChartDataESQ: function() {
    return this.Ext.create("Terrasoft.EntitySchemaQuery", {
        rootSchema: this.entitySchema,
        queryKind: Terrasoft.QueryKind.LIMITED
    });
},

...