SSAS tabular model database fails to restore due to out of memory error
We have 2 customers getting an out of memory error when they backup or restore a large tabular model database.
In the case of restoring a database backup, it is a 300GB .abf file restore on an SSAS server with 1TB of memory. When all the files are extracted to the data folder, the server needs to load the data, memory usage by SSAS goes up quickly then fail with error
Memory error: Allocation failure . If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine. An error occurred when loading VertiPaq data for the table 'ttttttt' in the database 'dddddd'. Database object is corrupt and cannot be queried. Delete this database from server, restore it from backup or create/process it.
When the error happens, there is still a few hundred GB of memory available. However, the Free memory is 0. It turns out during file extraction, SSAS starts to create a very large standby cache in Windows, even though the available memory can be 400GB, the 400GB is already used as standby cache.
We found if we clean the standby cache during the database file restore, it all works. I suspect the server cannot get free memory from the standby cache fast enough while the database process wants to allocate large amount of memory quickly.
Thanks to the author of the following blog, we were able to resolve the problem.
During the restore, we monitor the usage of standby cache, and clear the cache once it reaches a certain amount. At the end of the restore, SSAS requires large amount of memory rapidly to load data into memory. This is where it always failed until we keep the standby cache on cap.
Since the customer has no MD instance on the server to use the ASSP, I copy the clear cache functions into a console C# app. It works quite well.
Below is the perf mon graph on system cache vs SSAS memory usage on a successful restore when we clear the standby cache manually
For comparison purpose, below is perf mon counter when restore fails if the standby cache just keeps increasing. The available memory is still very high, but the free memory drops to 0 when the problem happens. There is no counter for free memory in perf mon, otherwise it will be a better graph.
There is a challenge to ASSP function ClearFileSystemCache on an SSAS tabular instance, because tabular instance does not support importing an external assembly.
To solve this problem, you can either install a second SSAS instance in multidimensional mode , or you can extract the code from ASSP https://asstoredprocedures.codeplex.com/SourceControl/latest then wrap the function calls in a .NET console application.