The monitor alerts, when the configured Total Memory Limit for SSAS instance exceeds the configured threshold, risking allocation of physical memory required for the operating system to perform its essential functions, at least 2 GB.
The monitor alerts, when the configured Total Memory Limit for SSAS instance exceeds the configured threshold, risking allocation of physical memory required for the operating system to perform its essential 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 the administrator, when adjusting Total Memory Limit settings after thoroughly reviewing SSAS Multidimensional Performance Guide.
At minimum, Total Memory Limit must allow sufficient space for the operating system to perform essential functions of memory management, along with driver and hardware interaction. If the OS has insufficient memory available for these essential functions, system instability or failure may 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 Total Memory Limit, representing 80% of physical memory available on the server. If other heavy consumers of memory (e.g., the SQL Server relational database engine) are present, this should be considered, and the memory limit settings should be 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, SSAS engine Total Memory Limit works somewhat differently. Total Memory Limit is the limit at which the server will begin to aggressively attempt to clear the memory as quickly as possible. Likewise, Low Memory Limit is the limit at which the server will begin to attempt to clear the memory from SSAS cache “lazily”, attempting to reduce impact on any existing sessions or queries already in progress. When Total Memory Limit is reached, the server performance can be affected, since the server will be aggressively clearing the memory from the cache. The server also supports Hard Memory Limit setting – a point at which the server will begin arbitrarily terminating the sessions, trying to free the memory. This will potentially cause failures for some users, and that is why Total Memory Limit is not enforced as a hard limit, since some queries or jobs may be memory intensive by their nature, and the OLAP engine will do its best to serve them within the limits it is configured to support.
This condition may be resolved by any of the following actions:
Lower Total Memory Limit below the configured threshold.
Add physical memory to the server to reduce Total Memory Limit setting, when expressed as a percentage of total available physical memory, below the threshold, expressed in GB.
Decrease the configured threshold to avoid the alert.
Disable the monitor entirely, if memory usage is not a concern on the server.
Memory configuration and sizing considerations in SQL Server 2008
TechNet documentation for Analysis Services memory properties
Configure Server Properties in Analysis Services
SSAS Multidimensional Performance Guide
Name | Description | Default Value |
Enabled | Enables or disables the workflow | Yes |
Generates Alerts | Defines whether the workflow generates an Alert | Yes |
Interval Seconds | The recurring interval of time in seconds in which to run the workflow. | 604800 |
Synchronization Time | The synchronization time specified by using a 24-hour format. May be omitted. |
|
Warning Threshold (GB) | The monitor alerts, when the configured Total Memory Limit for the operating system exceeds the configured threshold, risking allocation of physical memory required for the operating system to perform its essential functions, at least 2 GB. | 2 |
Timeout (seconds) | Specifies the time the workflow is allowed to run before being closed and marked as failed. | 300 |
Target | Microsoft.SQLServer.2008.AnalysisServices.Instance | ||
Parent Monitor | System.Health.ConfigurationState | ||
Category | ConfigurationHealth | ||
Enabled | True | ||
Alert Generate | True | ||
Alert Severity | Warning | ||
Alert Priority | Normal | ||
Alert Auto Resolve | True | ||
Monitor Type | Microsoft.SQLServer.2008.AnalysisServices.MonitorType.Instance.TotalMemoryConfiguration | ||
Remotable | True | ||
Accessibility | Public | ||
Alert Message |
| ||
RunAs | Default |
<UnitMonitor ID="Microsoft.SQLServer.2008.AnalysisServices.UnitMonitor.Instance.TotalMemoryConfiguration" Accessibility="Public" Enabled="true" Target="SQLAS!Microsoft.SQLServer.2008.AnalysisServices.Instance" ParentMonitorID="SystemHealth!System.Health.ConfigurationState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.2008.AnalysisServices.MonitorType.Instance.TotalMemoryConfiguration" ConfirmDelivery="false">
<Category>ConfigurationHealth</Category>
<AlertSettings AlertMessage="Microsoft.SQLServer.2008.AnalysisServices.UnitMonitor.Instance.TotalMemoryConfiguration.AlertMessage">
<AlertOnState>Warning</AlertOnState>
<AutoResolve>true</AutoResolve>
<AlertPriority>Normal</AlertPriority>
<AlertSeverity>Warning</AlertSeverity>
<AlertParameters>
<AlertParameter1>$Data/Context/Property[@Name='MemoryLimitHighGB']$</AlertParameter1>
<AlertParameter2>$Data/Context/Property[@Name='MemoryLeavesForOsGB']$</AlertParameter2>
<AlertParameter3>$Data/Context/Property[@Name='TotalPhysicalMemoryGB']$</AlertParameter3>
<AlertParameter4>$Data/Context/Property[@Name='MemoryUsageGB']$</AlertParameter4>
<AlertParameter5>$Data/Context/Property[@Name='MemoryUsageByOtherProcessGB']$</AlertParameter5>
<AlertParameter6>$Data/Context/Property[@Name='UsedPhysicalMemoryGB']$</AlertParameter6>
<AlertParameter7>$Data/Context/Property[@Name='MemoryLimitLowGB']$</AlertParameter7>
<AlertParameter8>$Data/Context/Property[@Name='MemoryLimitLowPercent']$</AlertParameter8>
<AlertParameter9>$Data/Context/Property[@Name='MemoryLimitHighPercent']$</AlertParameter9>
</AlertParameters>
</AlertSettings>
<OperationalStates>
<OperationalState ID="Warning" MonitorTypeStateID="NotConfigured" HealthState="Warning"/>
<OperationalState ID="Success" MonitorTypeStateID="Configured" HealthState="Success"/>
</OperationalStates>
<Configuration>
<Threshold>2</Threshold>
<IntervalSeconds>604800</IntervalSeconds>
<SyncTime/>
<TimeoutSeconds>300</TimeoutSeconds>
</Configuration>
</UnitMonitor>