MSSQL Analysis services optimization

[ADE1936]

Hello Everyone!

We are using Analysis services on one of the node(passive) Windows 2003 clustered servers.

Whenever we process a cube foe example 5 million records,
after sometime it gives an error low memory.
We have 4 GB (physical RAM) end we set the memory threshold
of Analysis Manager to 2047 based on what microsoft has suggested but still the problem persist.
I need help from any of the MSSQL Analysis or MSOLAP expert.


Regards,
Arwin

 

[MDXer]

Please provide some cube specifics.

Measure types, sum, count, distinct count?
Number of Aggregations.

You can actually take analysis services to 3gigs of memory but you should only need this much memory if processing extremely large cubes, multiple partitions or cubes with a distinct count measure that has a large number of distinct values.

5 million records really isn't that large of a recordset and should if designed right process relatively quickly.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[ADE1936]

Thanks Daffy Duck!!


What I need from you now is to configure the 3gig of memory
for Analysis Service, can you tell me how to do this?


Thanks,
Arwin

 

[MDXer]

I doubt it that throwing one more gig of memory at it will solve your problem. I'm willing to bet that the cube structure is at issue. I've built cubes that are built off 100 of millions and billion+ rows and never had a process use over 2 gigs of memory unless it involved a distinct count.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server\CurrentVersion\HighMemoryLimit.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck