SQL Analysis Aggregation Cache Test

An aggregation is a data structure that stores precalculated data that Analysis Services uses to enhance query performance. It is the job of the SSAS storage engine to fetch the data requested by a query and to aggregate it to the required granularity. Besides reading, writing, and aggregating data, the Storage Engine also performs caching. After the Storage Engine has answered a single request for data it stores the resulting subcube in cache so that if the same request is made in the future then it can be answered from there very quickly. In addition, in some cases the SE is also able to answer requests at a higher granularity from data that is currently in its cache - this is called cached aggregation. By increasing the likelihood of cached aggregation, the performance of large cubes willincrease. At the same time, if aggregate requests are not served from the cache frequently, it can have a negative impact on query performance. This is why, it is important that administrators continuously monitor the usage of the aggregate cache, detect poor cache usage, and see what can be done to reduce cache misses. This is where the SQL Analysis Aggregation Cache test helps!

This test tracks the usage of aggregate cache, and promptly alerts administrators to a poor cache hit ratio. Additionally, the test reveals if the lack of sufficient memory could have contributed to the inoptimal cache usage. The test also reports the rate of cache evictions, so that administrators can figure out if infrequent evictions is the reason for poor cache usage.

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	Measurement Unit	Interpretation
Current cache entries	Indicates the number of entries currently in aggregate cache.	Number
Memory used	Indicates the amount of current memory used by the aggregation cache.	KB	A high value is indicative of excessive memory usage by the cache. You may want to consider allocating more memory to the cache to ensure its uninterrupted use. Without adequate memory, the cache may not be able to hold enough entries, and hence may be unable to service requests.
Cache lookup rate	Indicates the rate of cache lookups.	Lookups/Sec	A high value is desired for this measure.
Miss rate	Indicates the rate of cache misses.	Misses/Sec	A low value is desired for this measure.
Miss ratio	Indicates the ratio of lookups to the sum of lookups and misses.	Percent	A high value is desired for this measure.
Direct hit rate	Indicates the rate of cache direct hits.	Hits/Sec	A cache hit indicates that queries were answered from an existing cache entry. A high value is desired for this measure.
Eviction rate	Indicates the rate of evictions from the cache.	Evictions/Sec	When Analysis Services memory utilization crosses the configured Low-limit, a process known as the “cleaner” is woken up and starts “evicting” older/lesser used objects out of memory on a periodic basis to make room for new objects. A high rate of evictions is ideal. This is because, if more objects are evicted from the cache, more space will be available for storing cached objects. This will help maintain a high cache hit rate going forward.
Direct hit ratio	Indicates the percentage of direct cache hits to the sum of direct cache hits and lookups.	Percent	A value above 80% is desired for this measure. A low value is indicative of poor cache usage, which translates to increased direct disk usage. Direct disk accesses are processor-intensive operations, which can adversely impact query performance. To improve query performance therefore, you need to enhance cache usage. For that, make sure that there is enough memory for the cache entries.