Dynamics AX 365 For Operations is leveraging the Resource Governor available in SQL Server. But before we dive in, let's first get the basics.
What is the Resource Governor?
In short the Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use. To be able to do that, SQL Server introduces the concept of Resource pools, Workload groups and a classification process.
- Resource pools
These represent a pool of physical resources on the SQL Server Instance. By default SQL Server has an Internal and Default resource pool.
- Workload groups
Workload groups are containers with session request that have the same classification. By default there is an Internal and Default group each resising in the corresponding resource pools.
- Classification process
This process will assign incoming sessions to workload groups depending on the characteristics of the session.
For fully documented information about the Resource Governor, please refer to the Microsoft Documentation. https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor
How Dynamics 365 FO leverages the Resource Governor
In production, the Resource Governor is configured as follows in terms of workload groups:
SELECT * FROM SYS.DATABASE_RESOURCE_GOVERNOR_WORKLOAD_GROUP
The kernel is providing SQL Server the context whether queries are coming from a batch process or running interactively and then SQL Server is able to allocate the workload to the right group. The way the kernel is doing this, is by using a classifier function that is assigned when the Resource Governor is enabled. The function itself is displayed below and shows the applicationTag determining the workload group:
Importance for Dynamics 365 For Operations load testing
When you are performing load tests on a non-production environment, it is important that this environment resembles production as much as possible. Deploying a performance environment as an add-on is a good start as that is a decently sized environment.
In production environments, the Resource Governor is enabled and configured as part of the deployment. Though, for non-production environments, it is possible that it is not enabled. If so, then this is a big difference as none of the workloads on SQL Server caused by Dynamics 365 FO will be throttled by SQL Server.
Enable Resource Governor on your environment
There are 2 parts of enabling the Resource Governor for Dynamics 365 FO: The AXClassificationTable table and the AXConfigureResourceGovernance stored procedure.
Step 1 : Update the AXClassificationTable table
Run the following T-SQL script to remove the ConfiguredDateTime values from the table. This is the table that will be read by the stored procedure in the next step creating the actual entries in the SQL Server tables related to Resource Governance. It is a good practice to also request the DSE team via a ticket to have the actual settings in the production environment so you can verify and update accordingly in the performance environment.
UPDATE AXCLASSIFICATIONTABLE SET CONFIGUREDDATETIME = NULL
Step 2 : Run AXConfigureResourceGovernance stored procedure
This stored procedure will collect the settings from the AXClassificationTable table and do the appropriate configurations in SQL Server. It will create a resource pool for batch and online, workload groups for batch and online and register the classification function which is dbo.AXClassifierFunction present in the Dynamics 365 FO database.
So that should do it and set you up for load testing with the use of the governor.
Please note that the statements and code shown in this post are purely intended for illustration purposes and might also be subject to change in the upcoming platform versions of Dynamics 365 For Operations.