Monitoring Cube Performance and Usage - Expert Cube Development with SSAS Multidimensional Models - page 336

If we experience out-of-memory conditions, increasing the size of the paging file is a
workaround that will at least prevent Analysis Services from crashing, even if it will
not help performance. If the problem is caused by too many concurrent processes,
another solution could be limiting the memory usage of these other processes (for
example, SQL Server does have settings to limit its memory allocation). However,
if the cause of the out-of-memory condition is a program or a service other than
Analysis Services with memory leaks, a better idea would be to investigate the cause
of the excessive memory allocation, replacing the leaking application version with a
fixed version or at least isolating it on another server.

As we said, it's not all that common to find an Analysis Services instance that
consumes too much memory, because TotalMemoryLimit is based on available
physical RAM and virtual memory is typically larger than physical RAM. Therefore,
if there are no other processes that are consuming significant amounts of RAM and
Analysis Services is the only "big service" running on a server, a reasonable amount
of paging file should be enough to avoid such situation. However in the real world,
we hardly ever find servers with only a single service running on them.

The TotalMemoryLimit property is not an absolute limit for the Analysis Services
Memory Manager: as we said, virtual memory allocated by other libraries (such
as stored procedures) used by Analysis Services is not under the control of the
Memory Manager. The Memory Manager itself might be unable to respect the
TotalMemoryLimit setting. Under heavy load, memory requests cannot be avoided;
sometimes memory requests come too fast to allow cleaning of older, rarely-used
allocations in a timely manner.

In both cases, the result is that the memory allocated exceeds the TotalMemoryLimit .
This is not an issue in itself, but it could break the balance of memory usage between
all the processes running on the server. Therefore, we cannot assume that Analysis
Services will always respect the limit we have defined, especially when the gap
between LowMemoryLimit and TotalMemoryLimit is tight.

Here are some possible causes of the type of heavy load on Analysis Services that
could result in abnormal virtual memory consumption:

• Parallel processing : If a server has many processors available, parallel

processing of cubes and dimensions will require a larger amount of memory.
To reduce memory usage, we can try to decrease the amount of parallelism
during processing.

• Too many queries and/or concurrent users : In this case, we should check the

usage of cache and aggregations (adding more aggregations and rewriting
queries and calculations if necessary), and make sure that sufficient memory is
available to Analysis Services to support the number of concurrent sessions.