In Dynamics AX there is a caching mechanism where tables can be configured to be loaded into memory and cached. This cache is limited to a certain amount of KB to prevent memory issues. The setting I'm talking about is called
entiretablecache and loads the whole table in memory as soon as a single record is requested.
Up to recently we relied on some scripts to verify the size of the tables that have this setting to see if the table size is above this limit.
Obviously, the application server is working with uncompressed data so the data size on disk in SQL Server is irrelevant. I need the actual size the uncompressed data will have.
I know of sp_estimate_data_compression_savings but as the name says, this is just an estimate.
I would prefer to have size as correct as possible.
The only way I could think of was some convoluted dynamic SQL creating uncompressed tables with the same structure as the compressed tables, inserting the compressed data in that shadow table and then check the size of that shadow table.
Needless to say, this is a bit tedious and takes a while to run on a database of several hundreds of GB.
Powershell could be an option, but I wouldn't like to iterate over all tables to perform a
select * on them to check the size in the script as that would just flood the cache and would probably take a long time too.
In short, I need a way to get the size for each table as it will be once uncompressed and with fragmentation out of the equation as presented to the application, if that's possible. I'm open to different approaches, T-SQL is preferred but I'm not opposed to Powershell or other creative approaches.
Assume the buffer in the application is the size of the data. A bigint is always the size of a bigint, and a character data type is 2 bytes per character (unicode). BLOB data takes the size of the data too, an enum is basically an int and numeric data is numeric(38,12), datetime is the size of a datetime. Also, there are no
NULL values, they are either stored as an empty string,
1900-01-01 or zero.
There is no documentation on how this is implemented, but the assumptions are based on some testing and the scripts used by PFE's and the support team (which also ignore compression apparently, since the check is built in the application and the app can't tell if the underlying data is compressed) which also check the table sizes. This link for example states:
Avoid using EntireTable caches for large tables (in AX 2009 over 128 KB or 16 pages, in AX 2012 over ‘entire table cache size’ application setting [default: 32KB, or 4 pages]) – move to record caching instead.