We are going to look at how you can review your ‘EntireTable’ Cache settings on tables within the AOT and why performance can be improved if your settings are incorrect.
When a table’s Cache Lookup in the AOT is set to ‘EntireTable’ it means a maximum of 2,000 records are loaded into the AOS servers cache for that table which saves having to go back to the SQL Server Database each time a query on that table is performed.
Each time a record is Inserted / Updated / Deleted the cache on the AOS has to be dropped and reloaded for that particular table, if you have a large table that is set to ‘EntireTable’ type caching and the table is being changed often this can cause detrimental effects on performance for your users as the table is having to be dropped from the cache and reloaded often and the AOS may have to go to back to the database anyway if the record isn’t in the cache.
We can check quite easily to see what tables are set in the AOT to ‘EntireTable’ caching and their associated row count in the database using the scripts below, I’ve provided two scripts one for real-time analysis and one which can be used with DynamicsPerf if you have this setup and configured for your environment:
/* AX EntireTable Caching Row Counts (Real Time).sql -- D.Coupland 24/03/13 -- Find rows counts of tables with cache lookup set to 'EntireCache' -- Investigate any row counts over 2,000+ to ensure they are not affecting performance */ USE <MyDax09Database> SELECT AOT.TABLENAME ,APPLICATION_LAYER = CASE WHEN AOT.TABID BETWEEN 1 AND 7999 THEN 'SYS' WHEN AOT.TABID BETWEEN 8001 AND 15999 THEN 'GLS' WHEN AOT.TABID BETWEEN 16001 AND 17999 THEN 'DIS' WHEN AOT.TABID BETWEEN 18001 AND 19999 THEN 'LOS' WHEN AOT.TABID BETWEEN 20001 AND 29999 THEN 'BUS' WHEN AOT.TABID BETWEEN 30001 AND 39999 THEN 'VAR' WHEN AOT.TABID BETWEEN 40001 AND 49999 THEN 'CUS' WHEN AOT.TABID BETWEEN 50001 AND 59999 THEN 'USR' WHEN AOT.TABID >= 65000 THEN 'System Table' ELSE Ltrim(Str(AOT.TABID)) END ,ST.ROW_COUNT FROM AOTTABLEPROPERTIES AOT INNER JOIN SYS.DM_DB_PARTITION_STATS ST ON OBJECT_NAME(ST.OBJECT_ID) = AOT.TableName WHERE ST.INDEX_ID < 2 AND AOT.CACHELOOKUP = 4 -- EntireTable Cache Enabled AND ST.ROW_COUNT > 0 -- Ignore Tables with no rows ORDER BY ST.ROW_COUNT DESC
Using DynamicsPerf DB
/* AX EntireTable Caching Row Counts (Using DynamicsPerf DB).sql -- D.Coupland 24/03/13 -- Find rows counts of tables with cache lookup set to 'EntireCache' -- Investigate any row counts over 2,000+ to ensure they are not affecting performance */ USE <MyDynamicsPerfDB> SELECT DISTINCT TD.TABLE_NAME ,TD.APPLICATION_LAYER ,TD.CACHE_LOOKUP ,ISC.ROW_COUNT FROM AX_TABLE_DETAIL_CURR_VW TD INNER JOIN INDEX_STATS_CURR_VW ISC ON TD.TABLE_NAME = ISC.TABLE_NAME WHERE ISC.ROW_COUNT > 0 AND -- Ignore Tables with no rows CACHE_LOOKUP = 'EntireTable' ORDER BY ISC.ROW_COUNT DESC
Download the scripts here: AX EntireTable Caching Row Counts (739 downloads)
I recommend you review any tables over 2,000+ rows in your ‘EntireTable’ caching strategy regularly ensuring you test any changes prior to making the changes in your Production Environment.
For further reading on AX 2009 Caching please take a look at the following articles: