Analysis Service Runs Out of Memory

Question

Post reply

Analysis Service Runs Out of Memory

huasheng

SSC-Addicted

Points: 478
More actions
September 23, 2008 at 7:43 pm

#124807

We are running SQL Server 2005 (with SP1) on Windows 2003 Server. The
server has 4GB memory. We have 9 main OLAP databases. All databases are updated and deployed by running XMLA script with fully process option.
Recently, we find Analysis Service runs out of memory every a couple days. Could anyone help on how to troubleshoot that?
Any comments would be greatly appreciated.
Huasheng

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

venu-413246 SSC Enthusiast Points: 172 More actions · Answer 1

You can set \3GB switch in boot.ini file to over come this issue.Let me know how it goes.

thanks,

venu

huasheng SSC-Addicted Points: 478 More actions · Answer 2

I changed it and also changed the page file size to 8092M (min and Max are same). But it does not work. The msmdsrv.exe consumes about 2900M memory when I monitor it in task manager.

Bascially, our cube is built incrementally daily. I am not sure if our SQL Server 2005 (SP1) should be updated to service pack SP2.

Any suggestions are welcome.

Thanks,

Dave Balsillie SSCertifiable Points: 5589 More actions · Answer 3

First, yes do the upgrade. There's important fixes in SP2.

Second. You don't mention how big your cubs are. Also, what does your partition and aggregation design look like? (How many attributes are on the cube BTW.)

Can you give us an idea of the size of the dimension of your cube? Do you have any complex calculations (i.e. complex SCOPE statements?).

Check the size of your aggregation cache. I suspect it's growing as people query the cube.

huasheng SSC-Addicted Points: 478 More actions · Answer 4

OLAP service still runs out of memory after I install SQL Server SP2. But cubes can be accessed when the service is out of memory (they are not accessible before SP2 was installed).

And I noticed the memory of msmdsrv.exe increases rapidly even there is no any operations on Analysis DB when some operations are done on other Databases (Not Analysis DB). The memory amounts to 2.8G in 4mins and decrease right away to 110M.

BTW: Our server has 19 Analysis database each of which contains about 20 Dim and 15 Facts table. Most of dim contains about 4 attributes. A few of them have about 10. And the biggest Dim table contains about 10M records.

Another problem occurred after SP2 was installed:

Event Type: Error

Event Source: .NET Runtime Optimization Service

Event Category: None

Event ID: 1101

Date: 12/13/2007

Time: 4:27:12 PM

User: N/A

Computer:

Description:

.NET Runtime Optimization Service (clr_optimization_v2.0.50727_32) - Failed

to compile: Microsoft.ReportingServices.QueryDesigners, Version=9.0.242.0,

Culture=neutral, PublicKeyToken=89845dcd8080cc91 . Error code = 0x80070002

Any comments would be greatly appreciated.

Dave Balsillie SSCertifiable Points: 5589 More actions · Answer 5

Ok, based on that information, that's a hefty cube. 10 Million rows isn a dimension isn't a trivial dimension, plus with 10 dimensions, the calculation space is huge. How many data rows do you have?

I'd review the design, to make sure it's appropriate. Also, you're going to want more memory. Remember, MSAS needs to pull in the dimension info to resolve queries, so it's not free.

I'd suggest that you may have created more dimension than you need. (What's the business application?)

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 6

It sounds like you have both SQL Server and Analysis services on the same machine.

Be sure to limit the memory for SQL server and adjust SSAS memory too. Consider what the OS will need too.

I'd consider upgrading to an x64 bit machine / version, along with more memory.

We run a quad core, on x64 bit, with 16 GB of ram. Much easier for us, and much better for our users.

Greg E

Bryan Evans Ten Centuries Points: 1311 More actions · Answer 7

I experienced similar behavior on one of our servers. I found the following article indicating cumulative update 2 for SQL Server 2005 contains a fix for situations where this can occur.

http://support.microsoft.com/kb/938077/

huasheng SSC-Addicted Points: 478 More actions · Answer 8

After I updated the SQL Server to SP2 and changed the memory from 2G to /3G, it became much better now. The next plan might be to update OS to x64 Win2008 and SQL 2008 and expand memory from 4G to 32G.

Thanks for your information.