THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Memory configuration pitfalls in SSAS

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.

Published Thursday, February 16, 2006 9:15 AM by Marco Russo (SQLBI)

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Stephan said:

Can you update the URL please, looks like points to the default page now. Thank you

September 28, 2009 10:33 AM

Marco Russo (SQLBI) said:

I just fixed the link.

Thank you!

September 28, 2009 12:18 PM

Leave a Comment


About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement