Author Bio

Steve Jones Editor at SQLServerCentral.com You can follow Steve on Twitter as way0utwest (www.twitter.com/way0utwest)

Analysis Services Memory Limits

By Steve Jones in SQL Musings | 08-27-2009 6:37 AM | Categories: Filed under: sql server 2008, Analysis Services

Rating: |

Discuss | 2,816 Reads | 49 Reads in Last 30 Days |5 comment(s)

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.

Comments

Rakesh said:

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

September 1, 2009 12:14 AM

Steve Jones said:

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.

September 1, 2009 8:00 AM

ics vs said:

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

September 23, 2009 4:54 PM

Steve Jones said:

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.

September 23, 2009 9:01 PM

RSage said:

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.

December 30, 2009 1:55 AM

SQL Musings

Analysis Services Memory Limits

Rakesh said:

Steve Jones said:

ics vs said:

Steve Jones said:

RSage said:

Blog Roll

Rockstart Blogger