TotalMemoryLimit Configuration

Summary

The monitor alerts when the configured TotalMemoryLimit for the SSAS instance exceeds the configured threshold, risking allocation of physical memory required for the operating system to perform necessary basic functions. Furthermore, the configuration does not account for potential memory consumption by processes other than the specific monitored instance, which must also be considered by an administrator when adjusting the TotalMemoryLimit settings after thoroughly reviewing the SSAS Multidimensional Performance Guide.

Causes

The TotalMemoryLimit must allow at minimum, sufficient room for the operating system to perform basic necessary functions of memory management, and driver and hardware interaction. If the OS has insufficient memory available for these essential functions, system instability or failure can occur. Therefore, the setting must be configured somewhat below 100%, to ensure these functions have sufficient physical memory available. If it is set too high, then this monitor will alert to warn of the condition.

The default configuration for SSAS is 80 for the TotalMemoryLimit, representing 80% of physical memory available on the server. If other large consumers of memory (e.g., the SQL Server relational database engine) are present, this should be considered and the memory limit settings lowered accordingly. If the number is raised too high, the OS may run low on physical memory and high amounts of disk paging may result, leading to inefficient performance on the server.

If a specific number of bytes is required, any value greater than 100 is interpreted as a number of bytes of physical memory.

Note: Unlike the SQL Server relational database engine, which supports a hard maximum memory limitation, the SSAS engine TotalMemoryLimit works slightly differently. The TotalMemoryLimit is the limit at which the server will begin to aggressively attempt to clear memory as quickly as possible. Likewise, the LowMemoryLimit is the limit at which the server will begin to attempt to clear memory from the SSAS cache “lazily”, attempting to reduce impact on any existing sessions or queries already in progress. When the TotalMemoryLimit is reached, the server performance could be impacted, since the server will very aggressively be clearing memory from the cache. The server also supports a HardMemoryLimit setting – a point at which the server will begin arbitrarily terminating sessions to try to free memory. This will potentially cause failures for some users and that is why the TotalMemoryLimit is not enforced as a hard limit, since some queries or jobs may by their nature simply be very memory intensive, and the OLAP engine will do its best to serve them within the limits it is configured to support.

Resolutions

This condition may be resolved by any of the following actions:

• Lower the TotalMemoryLimit below the configured threshold.

• Add physical memory to the server to reduce the TotalMemoryLimit setting when expressed as a percentage of total available physical memory, below the threshold, expressed in GB.

• Decrease configured threshold to avoid the alert.

• Disable the monitor entirely if memory usage is not a concern on the server.

External

Memory configuration and sizing considerations in SQL Server 2014

TechNet documentation for Analysis Services memory properties

Configure Server Properties in Analysis Services

TechNet documentation for SSAS Performance Counters

SSAS Multidimensional Performance Guide

Overrideable Parameters