SQL Analysis Memory Test

SQL Server Analysis Service is a memory intensive service. Even if SSAS is idle, it will consume around 40-50 MB of memory. It pre-allocates a modest amount of memory at startup so requests can be handled immediately. Additional memory is allocated as query and processing workloads increase. Administrators typically specify configuration settings to control the thresholds at which memory is released/allocated. If these threshold are not prudently set, then SSAS may not have sufficient memory to process its workload, which in turn can cause serious processing slowdowns. To promptly detect and avert such memory contentions and consequent processing bottlenecks, administrators should continuously monitor the memory usage of SSAS vis-a-vis the configured memory settings. This is where the SQL Analysis Memory test!

This test enables administrators to track the memory usage of SSAS alongside its memory configuration. This way, administrators can rapidly ascertain if SSAS is memory-hungry, and if so, alter the memory limits, so that SSAS has more memory at its disposal for processing requests.

Target of the test : A Microsoft SQL Analysis Services Server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for the SSAS server being monitored

Configurable parameters for the test
Parameters	Description
Test Period	How often should the test be executed.
Host	The host for which the test is to be configured.
Port	The port at which the specified Host listens.
Instance	The name of a specific SQL Analysis Services Server instance to be monitored. The default value of this parameter is “default”. However, if the Microsoft SQL Server hosting SSAS uses named instances, then do the following: Configure the Instance parameter with the name of the SQL Server instance that hosts SSAS. Do not change the default value of the Port parameter
SSAS Version	Specify the SSAS version being monitored. By default, 11 is displayed here, indicating that version 11 of SSAS is monitored by default.

Measurements made by the test
Measurement	Description	Unit	Interpretation
Memory limit hard	Indicates the hard memory limit.	MB	HardMemoryLimit defines a level at which the SSAS instance starts to reject requests completely due to memory usage. The default value for this setting is 0, which automatically places the threshold midway between the TotalMemoryLimit value and either the physical memory or virtual address space, whichever is smaller.
Memory limit high	Indicates the high memory limit setting.	MB	This is an upper threshold at which the SSAS instance begins releasing memory more aggressively to make room for currently executing requests and new high-priority requests. This limit applies to both multidimensional and tabular instances. The default value is 80 percent of either physical memory or virtual address space, whatever the smaller.
Memory limit low	Indicates the low memory limit setting.	MB	This is the lowest limit at which the SSAS instance will begin to release memory allocated to infrequently used objects.
Memory usage	Indicates the memory usage of the server process as used in calculating cleaner memory price.	MB	If the value of this measure increases consistently, it implies that the SSAS instance is memory-hungry. You may want to fine-tune the HardMemoryLimit, LowMemoryLimit, and/or the TotalMemoryLimit memory settings to fulfill the memory demands.
Memory reservation	Indicates the current memory reservation or quota.	MB	Memory reservation improves query reliability under server load, because a query with reserved memory is less likely to hit out-of-memory while running, and the server prevents one query from dominating entire server memory.
Current quota request blocked	Indicates the current number of quota requests that are blocked until other memory quotas are freed..	Number
Background cleaner memory	Indicates the amount of memory, in KB, known to the background cleaner	MB	SSAS has a special memory “cleaner” background thread that constantly determines if it needs to clean up memory. The cleaner looks at the amount of memory used. The value of this measure is the sum of the values of the Background cleaner memory non-shrinkable and Background cleaner memory shrinkable measures. If the value of this measure exceeds the value of the Memory limit low measure, the cleaner process starts to look for objects in-memory that it can eject. On the other hand, if the value of this measure exceeds the Memory limit high measure, the cleaner process gets much more aggressive. Likewise, if the value of this measure exceeds the value of the Memory limit hard measure, processes might be killed and you might receive out of memory errors.
Background cleaner memory non-shrinkable	Indicates the amount of background cleaner memory that is non-shrinkable.	MB	Non-shrinkable memory is not easily reduced as it is generally used for more essential system-related activities such as memory allocators and metadata objects.
Background cleaner memory shrinkable	Indicates the amount of background cleaner memory that is shrinkable.	MB	Shrinkable memory can easily be reduced and returned back to the operating system.
Memory allocated to filestore	Indicates the current memory allocated to filestore (file cache).	MB
Filestore data read rate	Indicates the rate at which data was read from filestore.	MB/Sec
Filestore data write rate	Indicates the rate at which data was written into filestore.	MB/Sec
Filestore page read rate	Indicates the rate of page reads from filestore.	Reads/Sec
Filestore page write rate	Indicates the rate of page writes to filestore.	Writes/Sec
Filestore page fault rate	Indicates the Filestore page fault rate.	Faults/Sec	Ideally, the value of this measure should be low. If the value exceeds 50 per paging disk, it is a cause for concern. In such a situation, you may want to see the configuration of the page file on the server.
Memory allocated to aggregation cache	Indicates the current memory allocated to aggregation cache.	MB
In-memory aggregation map file	Indicates the current size of in-memory aggregation map file.	MB
In-Memory dimension property file	Indicates the current size of in-memory dimension property file.	MB
In-Memory dimension string file	Indicates the current size of in-memory dimension string file.	MB
In-Memory map file	Indicates the current size of the in-memory map file.	MB
Page pool 64 allocation	Indicates the amount of memory borrowed from system.	MB
Page pool 64 lookaside	Indicates the current memory in 64KB lookaside list.	MB
Page pool 1 allocation	Indicates the memory borrowed from 64KB page pool.	MB
Page pool 1 lookaside	Indicates the current memory in 8KB lookaside list.	MB
Page pool 8 allocation	Indicates the memory borrowed from 64KB page pool.	MB
Page pool 8 lookaside	Indicates the current memory in 8KB lookaside list.	MB