OLAP Cube failure

[ProjectAnalyst]

Our cube was failing and giving Event ID 7688 in the application log, as well
as a CBS general failure (17001), and "There is insufficient system memory to
run this query." There were also many connection time outs and other errors,
making the problem difficult to isolate.

We determined that the SQL Server admins had recently configured SQL Server
to use less memory, which unfortunately was equal to the minimum memory
accessed by Analysis Services.

Essentially Analysis Services and SQL Server were fighting over the memory,
and AS was losing.

For an immediate fix, we lowered the minimum memory used by AS, and things
work again. We'll continue to monitor performance to see if we also need to
up the memory.

 

[Christophe Fiessinger [MSFT]]

Going forward you might want to either switch to 64 bit and add memory or
install Analysis Services on its own dedicated server again I highly
recommend x64

 

[ProjectAnalyst]

Thanks Christophe,

We actually have a 64 bit SQL Server, but only 4G of RAM....which provided
plenty of processing power before other applications began using the same SQL
Server. Now that SQL Server is much busier, I would really like to install AS
on a dedicated server. As part of our performance monitoring I'll try to
prove it's a good idea. I also put in a request for more memory today as
well.

 

[Gary L. Chefetz]

IMO, 4gb RAM is choking to a 64 bit system.

--
----------
Gary L. Chefetz, MVP
MSProjectExperts
Project Server Consulting: http://www.msprojectexperts.com
Project Server Training: http://www.projectservertraining.com
Project Server FAQS: http://www.projectserverexperts.com
Project Server Help Blog: http://www.projectserverhelp.com

 

[ProjectAnalyst]

I agree Gary. This was our first foray into 64 bit and SQL 2005, and Data
Analysis for that matter.

I hope our performance analysis proves the point. I'll share the results
here.

 

[ProjectAnalyst]

Okay, the solution was found.

We added SP3 to SQL Server 2005, and then activated the trace flags as
mentioned in this article:

http://blogs.msdn.com/brismith/arch...p-cube-builds-and-large-tempdb-revisited.aspx

Now our OLAP cubes run in 4 minutes instead of 30-60 minutes.