The monitor observes the memory usage by non Analysis Services processes on the server, to ensure the TotalMemoryLimit for Analysis Services is always available.
Memory usage on the server hosting the SSAS instance may impact the health and performance of the server by competing for shared resources from which the instance must also make allocations. This can lead to exhaustion of available physical memory on the computer, which can force the operating system to map virtual memory to the page file on disk, orders of magnitude slower than physical memory. For any business critical applications dependent upon a SSAS instance, the server should be configured such that other processes (e.g., anti-virus, disk defragmentation, other instances of SSAS or the SQL Server relational database engine, etc.) do not allocate excessive memory, leading to this circumstance.
The most common processes to significantly compete with SSAS instances for memory primarily include other instances of SSAS, the SQL Server relational database engine, or other database servers, since these tend to be the most intensive consumers of memory hosted on servers where SSAS is also hosted. Sometimes non-database software may also consume memory heavily as well of course. As such, memory considerations may frequently impact the configuration of a server.
The Task Manager can be used to examine memory usage by various processes on the server. On the Processes tab in Window Server 2008 R2, and Details tab in Windows Server 2012, one may sort by the Memory column to easily check to find the processes consuming the most memory on the system. If instance(s) of SSAS (msmdsrv.exe) or SQL Server relational database engine (sqlservr.exe) are consuming excessive memory, this is one simple way to identify their usage quickly, and of course, it is also possible to see if other processes on the system may be contributing to the condition. Subsequently, corrective actions (such as tuning memory usage of the instances by the SQL administrator, adding hardware, or reducing/dividing workload on the machine) may be devised accordingly.
Resolving issues of total server usage require administrators to understand and consider numerous factors including but not limited to workload requirements, user and job schedules, other scheduled processes on the server computer, and resources available. Actions to remediate excessive total memory allocations on the server computer may include any of the following actions:
Increase available physical memory on the server computer.
Decrease consumption by SSAS instance(s) or relational database instances by modifying design after consulting the SSAS Multidimensional Performance Guide, or contacting Microsoft support.
Decrease consumption by other processes through means specific to each, or by terminating those not required on the server.
Schedule jobs or reports on affected instance(s) to avoid simultaneous workload such that the server memory is excessively allocated.
Increase the 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.MemoryUsageByOtherProcessPercent | ||
Remotable | True | ||
Accessibility | Public | ||
Alert Message |
| ||
RunAs | Default |
<UnitMonitor ID="Microsoft.SQLServer.2008.AnalysisServices.UnitMonitor.Instance.MemoryUsageByOtherProcess" 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.MemoryUsageByOtherProcessPercent" ConfirmDelivery="false">
<Category>PerformanceHealth</Category>
<AlertSettings AlertMessage="Microsoft.SQLServer.2008.AnalysisServices.UnitMonitor.Instance.MemoryUsageByOtherProcess.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="Below" HealthState="Error"/>
<OperationalState ID="Warning" MonitorTypeStateID="Between" HealthState="Warning"/>
<OperationalState ID="Success" MonitorTypeStateID="Above" HealthState="Success"/>
</OperationalStates>
<Configuration>
<WarningThreshold>10</WarningThreshold>
<CriticalThreshold>5</CriticalThreshold>
<IntervalSeconds>900</IntervalSeconds>
<SyncTime/>
</Configuration>
</UnitMonitor>