I recently discovered a few pitfalls on memory configuration of SQL Server Analysis Services 2005: the main problem is that the default configuration (written by setup) doesn’t consider the actual physical resources (mainly RAM) of your server and this can cause some trouble. 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…

UPDATE: I completely changed this post due to incorrect information I wrote. Please read correct information here.

Memory / LowMemoryLimit: it’s a percentage of physical memory that defines the point at which server is low on memory. Default is 75 (75%) that’s is not good in two cases. First, if you have other processes on the same machine, you could prefer to lower this setting. Second, really important: if you have 8Gb of RAM and a 32-bit operating system, than the limit is defined by default at 6Gb, a measure that a 32-bit process can’t reach.. so SSAS will continue to allocate memory until an out of memory is raised by the operating system. A big problem! So if you have a similar configuration, consider to lower this setting to 18 (around 1.4Gb) or a bit higher if you enable the /3Gb option. To find the right number try to calculate the percentage of RAM memory necessary to have 1.4Gb as result.

Memory / TotalMemoryLimit: it’s a percentage of physical memory that the server will use at maximum. Problems are analogous to LowMemoryLimit: in a 32bit server with more than 2Gb of RAM you would definitely lower this number. In a 8Gb server I put the value of 20 (1.6Gb). To find the right number try to calculate the percentage of RAM memory necessary to have 1.4Gb as result.

I’ve been conservative in my calculations. A /3Gb option should allow higher numbers than mine. A 64bit server is immune of this kind of miscalculations…

UPDATE: I received this comment directly from Eric J. of Microsoft. Your recent post regarding AS2005 memory usage is incorrect.  Memory values are calculated as percentage of min( virtual, physical ), or if greater than 100.0 an absolute value is used (scientific notation is supported in the config file).  The defaults have been found to work well in a variety of scenarios, but there are cases where lower or higher values would work better.  AS2005 administrators may want to monitor the perfmon counters in MSAS2005 Memory.

I need to further investigate on it. More info coming soon, I hope.