Dynamics AX Table Caching: Basic Rules
This post provides some basic general guidance to get you started on setting table caching for custom tables, bearing in mind there will be exceptions. This should generally be defined at design time to avoid costly round trips to the database. As explained by Bertrand Caillet from our PFE (Premier Field Engineering) team:
“This is one of the most fundamental feature of the product today. The three tiers architecture of Dynamics AX allows you to define caching on AOS and client. Not using caching properly is the first root cause for performance.”
In AX 2012, table caching is more advanced than in previous versions, including support for joins, unique indexes, cross company queries, etc.
Cache settings for a table can be found in the following location in the application:
AOT > Data Dictionary > Tables > [TableName] > Properties > CacheLookup
You can use the script at the bottom of this post to check cache lookup settings for all tables using the “Performance Analyser 1.20 for Microsoft Dynamics” (DynamicsPerf) tool (partly based on the analysis scripts that come with this tool).
Set the appropriate table group depending on how the table is used; see the following article for further details for AX 2012 (for previous versions it is basically the same but with fewer table groups):
Following that, you can generally set table caching according to the table below, again bearing in mind there can be exceptions. Please refer back to the above links for an explanation of each cache lookup type.
| Table Group | Cache Lookup |
| Miscellaneous* | See notes below |
| Parameter | EntireTable |
| Group | Found |
| Main | Found |
| Transaction | NotInTTS |
| WorksheetHeader | NotInTTS |
| WorksheetLine | NotInTTS |
| Framework | N/A |
| Reference | Found |
| Worksheet | NotInTTS |
| TransactionHeader | NotInTTS |
| TransactionLine | NotInTTS |
* 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 in AX 2012, the cache limit is configurable for every table group 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 defaults are 32KB for AX 2012 RTM and 96KB for AX 2012 R2/R3.
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.
