SQL Resource Governor in combination with MSDyn365FO
Paul Heisterkamp
Solution Architect at GWS mbH | former Microsoft MVP | FastTrack Recognized Solution Architect | DevOps Enthusiast | OneVersion and Evergreen ERP Advocate
In Microsoft Dynamics 365 for Finance and Operation there different kinds of environments for testing and development purposes. You can have Tier1 to Tier5 environments, where Tier1 is the smallest configuration and Tier5 is the biggest one. From Tier2 and above you will get an Azure SQL behind the scenes with SQL resource governor enabled by default and also in production environments the SQL recourse governor is enabled by default.
The SQL resource governor is responsible for recourse allocation and reservation for business critical areas and replaces some settings you previously found in the table SysServerConfig. But the SQL resource governor only comes into play in case of recourse pressure, so when not enough recourse are available.
You can find the current setup for you environment in the table [SYS.DATABASE_RESOURCE_GOVERNOR_WORKLOAD_GROUPS]. The default setup is shown below:
This setup means that online sessions always get min. 60% of all available SQL resources and batch sessions always get min. 20% of all available SQL resources. So if online sessions need more than 60% and batch sessions need more than 40% both sessions types get throttled. If you are now wondering how do you know what sessions is a batch/online session you can check the function AXClassifierFunction. The function is called for every session to assign the corresponding resource group e. g.
- Application: Online ?= Resource group: Online
- Application: AxOnline = Resource group: Online
- Application: AxRetail = Resource group: Online
- Application: Batch = Resource group: Batch
- Application: axBatch = Resource group: Batch
- Application: AxServices = Resource group: Online
- Application: axOdata= Resource group: Batch
- Application: axDixf= Resource group: Batch
If you need to change the AXClassifierFunction or disable the resource governor for testing purposes you can do this with the following scripts after stopping the complete environment (AOS, Batch, DIXF, MR...)