The monitor observes the memory usage by non Analysis Services processes on the server, to ensure Total Memory Limit for Analysis Services is always available.
Memory usage on the server hosting 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 in such a way 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, SQL Server relational database engine, or other database servers, since these tend to be the most intensive consumers of memory hosted on the servers, where SSAS is also hosted. Sometimes non-database software may also heavily consume the memory. As such, memory considerations may frequently affect the configuration of a server.
Besides, the value of free unreserved memory (FreeUnreservedPercent parameter) may become negative. This parameter is calculated as follows: 100% of the total memory minus high memory usage by the instance (SSAS), and minus memory usage by other processes. The reasons may be:
Total Memory Limit parameter is set incorrectly in the configuration file
Total memory used by other processes and the preset limit for SSAS exceeds 100%
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 2008, one may sort by the Memory column to find the most memory consuming processes on the system. If instance(s) of SSAS (msmdsrv.exe) or SQL Server relational database engine (sqlservr.exe) consuming excessive memory, this is a simple way to identify their usage quickly, and, of course, it is also possible to see, if other processes on the system are 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 the 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 reviewing SSAS Multidimensional Performance Guide, or contacting Microsoft support.
Decrease consumption by other processes through means specific to each of the processes, or by terminating those not required on the server.
Schedule jobs or reports on the affected instance(s) to avoid simultaneous workload during periods, when the server memory is excessively allocated.
Tune 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
Configure Server Properties in Analysis Services
SSAS Multidimensional Performance Guide
Name | Description | Default Value |
Critical Threshold (%) | Health State changes to Critical, when Free Unreserved (%) drops below the threshold. | 5 |
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. | 900 |
Number of samples | Health State changes if the number of threshold breaches is greater than or equal to the Minimum Number of Breaches. | 4 |
Synchronization Time | The synchronization time specified by using a 24-hour format. May be omitted. |
|
Timeout (seconds) | Specifies the time the workflow is allowed to run before being closed and marked as failed. | 300 |
Warning Threshold (%) | Health State changes to Warning, when Free Unreserved (%) drops below the threshold, but is still higher than Critical Threshold (%). | 10 |
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/>
<TimeoutSeconds>300</TimeoutSeconds>
</Configuration>
</UnitMonitor>