We have a 64-bit SQL Server 2012 SP4 Enterprise Edition instance with Analysis Services (Analysis Services is running on the same VM as the SQL Server database engine) where the memory usage of the Analysis Services process continues to grow until the point where cube processing begins to fail with a report of "The operation has been cancelled because there is not enough memory available for the application". Once this happens our only option appears to be to restart the Analysis Services process and begin the cycle again.
I have experimented with changing the low/total/hard memory limits in Analysis Services, but so far have not found anything that works for more than about 5-6 days.
What I have found is that the performance counter for "Cleaner Memory nonshinkable KB" continues to grow day to day, which is not something that I would expect.
Would anyone have any insights or suggestions to help diagnose this issue?
Some addition information:
- The VM has 20 GB of RAM allocated to it.
- The SQL Server is currently set to limit memory usage to 12 GB.
- The Analysis Services low/total/hard memory limits are currently set to 40/50/70 (percent).
- The SQL Server has about 20 databases of moderate size totaling no more than 100 GB.
- There are five Analysis Services databases / cubes.
- We use multidimensional models.
- The cubes are fully processed overnight on a daily basis.
- Proactive caching is used to keep the cubes updated during the day.
- Web sites access the cubes via the OLAP "pump" (msmdpump.dll).
- This VM was recently set up to replace a physical server that had a very similar configuration except for having a few more GB of RAM and a less recent service pack of SQL Server. That physical server had been running for years and we have never experienced this issue with it. Unfortunately, that server failed and is no longer bootable (disk issues) and the person who originally configured Analysis Services on it so far has not been able to remember any details about the setup that has been useful.
