SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Analysis Services Memory Limits

I attended a presentation recently from Steven Wright of SQL Sentry on Analysis Services (SSAS) memory management and it was really interesting. I haven't done much work at all with SSAS, mostly goofing around with basic cube setups, so I haven't really had to administer a production instance. And I haven't run into memory issues, which look like they could be a regular part of your day.

SSAS manages memory much differently from SQL Server, and there's a lot to learn, but there was one amazing fact to me about the settings. There are two limit settings, the Low Memory Liimit and the Total Memory Limit, both of which determine how aggressively SSAS starts to try and clear out objects from memory to free it up. These are settings you make on the server, either in the properties or an .ini file (what is this SSAS 3.1?) and the defaults are 75 for the low limit and 80 for the total.

These are documented in Books Online, and it says this is the percentage of total physical memory.What is doesn't say, however, is this: If you enter a number greater than 100, the value is interpreted as the "bytes" of memory to be used. That's bytes with a little "b", not kb, not MB, but b. It's documented here by Greg Gonzalez.

You'd think that would be something they want to call out in the documentation. A few people have been bitten by this thinking it was kb or MB, since most counters are expressed in one of those scales.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Posted by Rakesh on 1 September 2009

I was hoping to get much more info regarding memory in SSAS from the post...

Posted by Steve Jones on 1 September 2009

sorry you didn't like it. Books Online is a good place for more in depth looks at things. Or the SQL Cat.com site.

Posted by ics vs on 23 September 2009

Sounds like enough information in a condensed format and just what I would care to know about. Good job Steve.

Posted by Steve Jones on 23 September 2009

If you get a chance, check out Steve from SQLSentry's presentation. It's pretty good. He's doing it remotely via live meeting for the Baton Rouge group. You can register with them.

Posted by RSage on 30 December 2009

So if you did enter 100 would SSAS interpret that as 100% of the CPU? Which to me sounds crazy anyway. Someone is just  asking to lock up the server when a client process a cube or something.

Leave a Comment

Please register or log in to leave a comment.