The monitor reports a warning when memory allocations by the SSAS instance surpass the configured WarningThreshold, expressed as a percentage of the TotalMemoryLimit setting for the SSAS instance. The monitor issues a critical alert when these allocations surpass the configured CriticalThreshold.
The monitor reports a warning when memory allocations by the SSAS instance surpass the configured WarningThreshold, expressed as a percentage of the TotalMemoryLimit setting for the SSAS instance. The monitor issues a critical alert when these allocations surpass the configured CriticalThreshold.
SSAS can approach or exceed the TotalMemoryLimit setting under some circumstances. In some cases, a single operation requires memory beyond these limit, and despite the server’s best effort to clear memory from other less immediate tasks, allocations may sometimes necessarily exceed the configured limits, or else the operations must be forced to fail.
This can occur when processing large dimensions or fact data, particularly if there exist attributes with data types consuming large amounts of space (e.g. long strings, binary data, etc.), or if the dimension contains numerous members. Also, queries executed on the server may cover very large spaces, particularly if multiple dimensions are being cross joined or significant calculations are required to derive results for cells not aggregated previously by a wise administrator, or simply not possible to aggregate in advance.
When memory limits for a server are exceeded, there are numerous approaches administrators may use to remediate the issue. After considering the underlying activity causing the memory usage, requirements for workload on the server, available resources, and other factors, as well as reviewing and considering the SSAS Multidimensional Performance Guide thoroughly, or consulting Microsoft support, as necessary, any of the following approaches may be used to address the issue:
Identify the session(s) responsible for the most significant memory allocations, and manually terminate them.
Identify the session(s) responsible for the most significant memory allocations, and address them on a case-by-case basis to reschedule processing jobs or reports, pre-allocate with cache warming queries during times of lower utilization, or by designing specific aggregations in advance, to be built during processing, and hence reducing memory consumption by user queries subsequently.
Increase physical memory on the host computer.
Decrease memory usage by other processes on the host computer, such as instances of the SQL Server relational database engine or other instances of SSAS.
Increase the TotalMemoryLimit or configured thresholds for the alert to avoid the condition.
Disable the monitor entirely if memory usage is not a concern on the server.
TechNet documentation for Analysis Services memory properties
Target | Microsoft.SQLServer.2008.AnalysisServices.Instance | ||
Parent Monitor | System.Health.PerformanceState | ||
Category | PerformanceHealth | ||
Enabled | True | ||
Alert Generate | True | ||
Alert Severity | Error | ||
Alert Priority | Normal | ||
Alert Auto Resolve | True | ||
Monitor Type | Microsoft.SQLServer.2008.AnalysisServices.MonitorType.Instance.MemoryUsagePercent | ||
Remotable | True | ||
Accessibility | Public | ||
Alert Message |
| ||
RunAs | Default |
<UnitMonitor ID="Microsoft.SQLServer.2008.AnalysisServices.UnitMonitor.Instance.MemoryUsage" Accessibility="Public" Enabled="true" Target="SQLAS!Microsoft.SQLServer.2008.AnalysisServices.Instance" ParentMonitorID="SystemHealth!System.Health.PerformanceState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.2008.AnalysisServices.MonitorType.Instance.MemoryUsagePercent" ConfirmDelivery="false">
<Category>PerformanceHealth</Category>
<AlertSettings AlertMessage="Microsoft.SQLServer.2008.AnalysisServices.UnitMonitor.Instance.MemoryUsage.AlertMessage">
<AlertOnState>Error</AlertOnState>
<AutoResolve>true</AutoResolve>
<AlertPriority>Normal</AlertPriority>
<AlertSeverity>Error</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="Error" MonitorTypeStateID="Above" HealthState="Error"/>
<OperationalState ID="Warning" MonitorTypeStateID="Between" HealthState="Warning"/>
<OperationalState ID="Success" MonitorTypeStateID="Below" HealthState="Success"/>
</OperationalStates>
<Configuration>
<WarningThreshold>80</WarningThreshold>
<CriticalThreshold>95</CriticalThreshold>
<IntervalSeconds>900</IntervalSeconds>
<SyncTime/>
</Configuration>
</UnitMonitor>