Sql-server – Memory usage grows on SQL Server Analysis Services 2012 until cubes fail to process

sql server

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.

Best Answer

From a comment by Robin Blondon:

I tested with with SP1 through SP4 and it looks like SP4 is the only one that has this issue. I submitted a bug report On Azure Feedback.

The suggested advice would be to upgrade to a newer version of SQL Server Analysis Services, since SQL Server 2012 is out of mainstream support:

Related Question