SQL Resource Governor in combination with MSDyn365FO

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:

Es wurde kein Alt-Text für dieses Bild angegeben.

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…)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s