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.

NOTE Only Select-requests can be transfered to the separate query pool and only if they are not part of the transaction.

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. Bpm'online 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 = bpmonline” property in the connection string of the ConnectionStrings.config file will result in it being changed to “bpmonline_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=bpmonline; Data Source=dbserver\mssql2016; Initial Catalog=BpmonlineSolution; 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.

ATTENTION No resource restriction occurs in this case. The application only provides an opportunity to use a mark for the ranking of connections in Resource Governor. Please note that the work of Resource Governor is difficult to see on an unloaded server with “short” requests. The effect is noticeable when working with a fully loaded database, and when the “heavy” request is being processed for a long time.

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 Using the DBExecutor for working with the database. 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
    });
},

...

ATTENTION If there are nested calls to userConnection.EnsureDBConnection(QueryKind) in your code, make sure that you use the same QueryKind value at all nesting levels.