Memory Management in SQL Server Analysis Services

HI Friends,

SQL Server Analysis Services (SSAS) Memory works pretty much like the database engine. SSAS, by default, offers automatic memory management. It asks for memory from the Operating System when it needs more memory and returns back to the OS, when OS needs it for other processes. However, you can tweak how much memory is available to SSAS by modifying some server properties.

Before you start playing around with these server properties, lets understand some basics.

This applies to SQL Server 2005 & 2008.

SSAS memory demands can be classified into two categories:

Querying: SSAS needs memory during query execution. Memory is used at various stages: Storage Engine cache is used to store measure group data for fast data retrieval. Query Execution Engine cache is used to store calculation results for fast calculation. Memory is also needed to load dimension data. Thus, sufficient memory is required at the time of querying.

Processing: At the time of processing, sufficient memory is required to process fact rows, store indexes and aggregated data before writing it to the disk. If sufficient memory is not available, then the job will be blocked.

Now, lets see the tweaking aspect:

By default in 32 bits systems, SSAS can only address 2 GB memory space. If your server has more than 2 GB of physical memory (for eg, 4 GB), you can include a /3GB switch in the boot.ini file to make sure that SSAS can address up to 3 GB.

Its not possible to address more than 3 GB on 32 bit systems. So, if you have 8 GB RAM on your server and you have a requirement of using that much physical memory for processing and/or querying, you are advised to upgrade to 64 bit systems.

There are 2 important server properties that you can tweak:

Memory\TotalMemoryLimit: This represents the upper limit of memory that the server uses to manage many of the Analysis Services operations.Any value between 0 and 100, means a percentage of total physical memory. If the value is set to above 100, it means absolute memory value in bytes. The default value for Memory\TotalMemoryLimit is 80, meaning 80% of the physical memory of the server. You can tweak this value as per the memory demands of SSAS. But you need be careful as modifying this property may affect other applications on the server including SSAS service itself.

   

Memory\LowMemoryLimit represents the lower limit of memory that SSAS uses to manage many Analysis Services operations. A value between 0 and 100 is interpreted as a percentage of total physical memory. If the value is above 100, it means absolute memory value in bytes. The default value is 75, meaning 75% of the amount of physical memory.

There is another important server property:

The OLAP\Process\BufferMemoryLimit property controls the amount of memory that is available to SSAS for processing cubes. And this is per processing job. This property, just like the other two, accepts a value between 0 and 100 meaning a percentage of total physical memory. If the value is above 100, it means an absolute value of bytes. The default value is 60, which indicates that a maximum of 60% of the total physical memory can be used. For most deployments, the default value of OLAP\Process\BufferMemoryLimit provides sufficient processing performance.

So, I hope you have got a quick good hang of these server properties related to memory management. So where can you modify these properties from?

1. Connect to SSAS instance using SQL Server Management Studio.

2. Right click the instance name/server name in the Object Explorer.

3. In the General page you can see all the properties. Click on “Show Advance Properties” and you shall be able to see all the properties mentioned above.

See fig below:

1_Memory_Management_in_SQL_Server_Analysis_Services

 

 

   

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

Leave a Reply

Your email address will not be published.