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:

Real-Time Analysis:

/* 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 (1039 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:

http://msdn.microsoft.com/en-us/library/bb314693(v=ax.50).aspx

http://dynamicsuser.net/forums/p/30043/157813.aspx