SQL Server Analysis Services Server Memory Issue

Sometimes we might have noticed that SSAS is consuming too much memory in the server and creating a deadlock kind of situation where the only solution is to restart the SSAS service or restarting the server.

This situation arises when bulky MDX queries are running and trying the fetch the large volume of data. Since in the SSAS the MDX queries are constructed with in the memory, the MDX query requesting for the large amount of data can be very much memory intensive. We can avoid these kinds of situations by changing some default memory settings in the server. Before we talk about the solution I think it is very important to understand some of the memory setting properties within the SSAS. I have captured their details below as per described in MSDN.

LowMemoryLimit

Specifies the amount of memory allocated by Analysis Services at start up. When this limit is reached, the instance will start to slowly clear memory out of caches by closing expired sessions and unloading unused calculations. The server will not release memory below this limit. The default value is 65; which indicates the low memory limit is 65% of physical memory or the virtual address space, whichever is less.

TotalMemoryLimit

Defines a threshold that when reached, causes the server to deallocate memory more aggressively. The default value 80% of physical memory or the virtual address space, whichever is less.

Note that TotalMemoryLimit must always be less than HardMemoryLimit

HardMemoryLimit

Specifies a memory threshold after which the instance aggressively terminates active user sessions to reduce memory usage. All terminated sessions will receive an error about being cancelled by memory pressure. The default value, zero (0), means the HardMemoryLimit will be set to a midway value between TotalMemoryLimit and the total physical memory of the system; if the physical memory of the system is larger than the virtual address space of the process, then virtual address space will be used instead to calculate HardMemoryLimit.

In order to fix the memory freezing problem, we should change the default Memory\HardMemoryLimit setting.  We should keep this value between the Memory\TotalMemoryLimit and 100. Since the TotalMemoryLimit is 80 by default, then in this case we can set the HardMemoryLimit to 90. What will happen is anytime when the memory exceeds 90%, the SSAS will start cancelling the queries.  The users might see the error message saying “The operation has been cancelled due to memory pressure”.

In order to set the Memory\HardMemoryLimit property to 90, please follow the steps below

1.       Login in the SSAS using the SQL Management Studio.

2.       Right Click on the Server Name and Go to Properties.

3.       Look for the “Memory\HardMemoryLimit” property and set the value to 90. By default it is 0.