Dynamics 365 for Finance and Operations Table Caching: Basic Rules

In Dynamics 365 for Finance and Operations, the basic rules are still the same as with previous versions. The documentation links I have provided below are also specifying AX 2012 but again still relevant. The key difference is how the data will be collected – see further down.

Introduction

What you’ll find here is some basic general guidance to get you started on setting table caching for custom tables, bearing in mind there will be exceptions. Table caching should generally be defined at design time to avoid costly round trips to the database.

Essentially there are two types of table caching as explained on msdn:
Set based caching 
Single record caching

Cache settings for a table can be found in the following location in the application:
AOT > Data Dictionary > Tables > [TableName] > Properties > CacheLookup

Changes like this should be made by a developer in accordance with best practice guidance.

Data collection

The difference now is that the AOT export class from the Performance Analyzer is obsolete for our version here, so I have created a new AOT export based on this, which you can find on Github.

As before, this solution combines a Dynamics 365 for Finance and Operations package with a SQL solution file, so therefore assumes a basic knowledge of both. You can find the steps to install and use it here:
https://community.dynamics.com/365/financeandoperations/b/performancetipsandtricks/archive/2019/01/05/table-metadata-export-for-dynamics-365-for-finance-and-operations-d365fo 

Download link: https://github.com/glenturnbullazure/D365FOPerf 

Note: alternatively, it is possible to review some of the cache settings using the Customisation Analysis Report, however the above job reports on every table (as opposed to exception based) and can currently enable us to catch more exceptions.

Best practices

Set the appropriate table group depending on how the table is used; see the following article for further details:
 
Table and table group reference 
http://technet.microsoft.com/en-us/library/gg731855.aspx 

Following that, you can generally set table caching according to the table below, again bearing in mind there can be exceptions. Please refer to the above links for an explanation of each cache lookup type.

* All newly created tables default to a table group of Miscellaneous. Ideally don’t use this table group for custom tables.

Finally, bear in mind that the cache limit is configurable in the server performance settings:

System Administration > Setup > System > Server Configuration > Performance optimisation tab

Entire table cache size determines in kilobytes how much data is cached in memory before spilling to disk.

The default is 96KB for Dynamics 365 for Finance and Operations.

The record cache limits define (per table group) the number of records stored in the server side cache and the client record cache factor defines based on that the number records stored in client cache, e.g. server side cache of 2000 (default) and client record cache factor of 20 (default) means 100 records are stored in client cache. Each AOS server can have its own cache settings. The basic rule here it is to keep the defaults unless performance testing proves it addresses a specific issue.