In most sites I’ve been working at, developing Analysis Services solutions, most people were treating the Analysis Services server as a black box. Unlike the Database Engine, where DBAs knew their way around server and database settings and how to configure server properties in order to achieve high performing databases and applications, with Analysis Services it is usually not the case. In this article (and in future articles) I’ll try to shed some light on some important Analysis Services server properties that you can, as an Analysis Services administrator, tweak and fine-tune to get your Analysis Services engine running faster.
Memory utilization is one of the first things you need to look into when you configure your Analysis Services server and this is indeed one of the most important settings in Analysis Services. Firstly, in order to capitalize on the total amount of memory installed on the server, you need to carefully consider whether you want to share the machine Analysis Services resides on with other servers and/or applications. Analysis Services is a major consumer of memory and if you can, try your best to keep it on a standalone box without competition for memory from other consumers.
There are two basic memory related server properties in Analysis Services: the “MemoryLowMemoryLimit” and the “MemoryTotalMemoryLimit”. For each of these properties, if you set their value to anything under 100, Analysis Services will treat the value in percentage terms (i.e. if you set it to 80, it means 80% of available memory on the box). These two settings are not absolute limits for the engine. Analysis Services can definitely operate below its “low limit” and above its “total limit”. However, these properties mark the “memory working range” for Analysis Services, and by evaluating current memory consumption to these benchmarks, Analysis Services can better handle its operations. For example, if Analysis Services uses less than its “low limit” threshold, then the engine “knows” that there is no memory pressure at all and it can continue to cache data, result sets and use memory for processing objects. If Analysis Services uses memory between the two thresholds, then Analysis Services realizes that some memory pressure exists and it needs to start clearing objects from cache, where and when it can do so. If Analysis Services is using memory above the “Total limit” threshold, then it starts clearing its cache continuously, until it uses less than the “Total limit” threshold defined. When Analysis Services needs to consume more than its “Total limit” threshold for processing Analysis Services objects, it will also start using disk as an alternative to memory, an action that hinders performance greatly as well.
So, how would you know if there is memory pressure on your Analysis Services box? You can use Performance Monitor counters to track memory consumption using the “Memory Usage KB” counter. Identifying timeframes where Analysis Services consumed more than its “Low Limit” threshold and above its “Total Limit” threshold.
So far we have identifier the so-called general memory settings for an Analysis Services server. There are operations-specific memory settings as well, which definitely worth mentioning as well. When Analysis Services processes objects (dimensions, partitions and aggregations) it uses memory. The server property “OLAPProcessBufferMemoryLimit” controls the amount of memory Analysis Services can use to cache data while processing Analysis Services objects. Its value is also defined in percentage terms from 0 to 100. The following server properties control the amount of memory Analysis Services can use to calculate aggregations: “OLAPProcessAggregationMemoryLimitMax” and “OLAPProcessAggregationMemoryLimitMin”.
In order to identify memory pressure when calculating aggregations, you need to monitor the “Proc AggregationsTemp file bytes written/sec” Performance Monitor counter. If this counter shows any activity during aggregation calculation, it means that Analysis Services does not hold sufficient memory to calculate the aggregations in memory and uses disk as an alternative storage and calculation space.
Memory is an important resource for Analysis Services and needs to be configured and monitored continuously in an Analysis Services environment. Proper configuration can boost the engine’s performance, reduce processing and querying time and greatly enhance the end-user overall experience.