Candidate for EntireTable CacheLookup on Dynamics AX

When deciding which table is suitable for EntireTable caching in Dynamics AX, the rule is the 128k size limit (http://msdn.microsoft.com/en-us/library/bb314630(v=AX.50).aspx). With the note in MSDN - "Avoid using EntireTable caches for large tables because once the cache size reaches 128 KB the cache is moved from memory to disk. A disk search is much slower than an in-memory search.", so, how do we decide which table should be cached? The easiest way is to look at the page count of the table itself. In SQL Server, 1 page == 8kb, which means 128k == 16 pages. With the help of 'sys.dm_db_index_physical_stats' we can list out all the tables that is less than or equal 16 pages, these will be the candidate for further analysis. Do not change them all to EntireTable CacheLookup, there're further analysis required.


USE <databasename>
GO
SELECT OBJECT_NAME(object_id),
       [object_id],
       index_id,
       index_level,
       page_count,
       avg_fragmentation_in_percent
  FROM sys.dm_db_index_physical_stats(DB_ID('<databasename>'),NULL,NULL,NULL,DEFAULT)
 WHERE page_count  > 0
   AND page_count <= 16
GO

If the system is still in implementation phase, most likely the data is quite crappy, insufficient, or with just a small set of test records, they're not helping in decision making. With the list of tables returned from the above query, do further analysis and ask yourself:

- Is the table potentially growing larger over time?

- Are the data quite static? (avoid doing EntireTable cache on small table, but has high change rate)

- Is the table (unique) index correctly set? (Eg. to the ::find() method or other query's unique criteria)

There's one thing that I'm not quite sure yet, that is whether the 128k limit is per index or per table.

You can test it out by capturing the trace and look at it with Trace Parser.


*NOTE: The query took around 30 minutes to run on my system (~500GB database), you might want to choose a better timing to run it.


To run the query against one single table, replace the second parameter to the object Id of the table.


From: sys.dm_db_index_physical_stats(DB_ID('<databasename>'),NULL,NULL,NULL,DEFAULT)
To: sys.dm_db_index_physical_stats(DB_ID('<databasename>'),OBJECT_ID('<tablename>'),NULL,NULL,DEFAULT)