I recently had some problem with memory configuration of SQL Server Analysis Services 2005 and I’d like to share lessons I got. This post is a replacement for my previous post, that I striked to keep history of some bad assumptions I initially made.

SSAS have some setting that controls the way memory is managed by SSAS2005. Default values probably doesn’t take care of concurrent applications and you could want to change them on a server running other services. Settings I’m talking about are defined as parameters of a single instance of SSAS: please note that MS suggest to not touch these settings…

Memory / LowMemoryLimit: it defines the value of physical memory that defines the point at which server is low on memory. Values greater than 100 are absolute values (scientific notation is supported in the config file, msmdsrv.ini). Values under 100 are calculated as a percentage of memory: reference value of memory (that corresponds to 100) is calculated as the minimum between the physical memory and the virtual memory available for a process. We have several cases (for semplicity I show the memory limit calculation for an hypotetic 100 value of the parameter): a 8Gb RAM 32bit Windows has a 2Gb limit (or 3Gb if you have /3Gb option enabled); a 8Gb RAM 64bit Windows has a 8Gb limit; a 1Gb RAM 32bit Windows has a 1Gb limit.

Default is 75 (75%) that is good most of the time, but if you have other processes on the same machine, you could prefer to lower this setting. If you want to know what the actual and running absolute limit is, you can get the exact running value reading the MSAS 2005 Memory / Memory Limit High KB performance counter.

Memory / TotalMemoryLimit: it’s a percentage of physical memory that the server will use at maximum. Behaviour and setting considerations are analogous to LowMemoryLimi..

I had notice that in some cases PSS suggested to lower these settings to avoid an Out Of Memory error of SSAS 2005. After my previous wrong assumptions, Eric J of Microsoft helped me to understand what BOL still doesn’t explain well. Eric wrote some other hints that we can share and index for future references.

Eric: You should assume they AS2000 and SSAS2005 are completely different products.  There are some similarities in settings but many differences.

Eric: Out of memory error could have been caused by many things, but basically memory could not be allocated from the OS.  Changing config values also impacts the point when the cleaner thread removes “shrinkable” memory.  Watch the perfmon counter “MSAS 2005:MemoryCleaner Memory Shrinkable KB”, and nonshrinkable.

Eric: To observe effects while you change LowMemoryLimit settings you can:

1. Open msmdsrv.ini in notepad, change value, and save file.
2. Open perfmon, report setting, view “MSAS 2005:MemoryMemory Limit High KB”
3. Observe the above listed value.

I already suggested to Eric that BOL could be improved on this topic (existing information are not so clear).