Monday, May 28, 2012

SSAS: Memory error

Memory error: The operation cannot be completed because the memory quota estimate (XXXXMB) exceeds the available system memory (XXXXMB).

SSAS throws this error while processing one of the dimension in Cube when memory need for the operation exceeds memory available for SSAS, primarily due to size of dimension.


To solve this error there are three options

A)  Increase size of RAM. (I know, not easy to implement in most organizations, so we go to next option)

B)  Divide you Dimension table so as they are small enough to complete processing in given memory.
for example if Dimension is holding Customer Names then divide DimCustomer into two table, one with Customer names starting with A-M and another with N-Z. It will require updation of fact table with new Customer Primary key.

(Again will require time and design changes may not be possible straight away, so we go to next option)

C)  BufferMemoryLimit: There is always an easy fix but not permanent so as our next option.

For Processing requirement by default SSAS provides 60% of memory available to it. For a fix we can increase Buffer Memoryt Limit  percentage so as to accommodate the processing requirement.

It is specified in msmdsrv.ini file in Program Files/SQL Sever/.../SSAS/Config/ folder at

 
   


You can increase this BufferMemoryLimit to you suitability.
a) Between 0-100, it is in Percentage and allocated % of Memory to the operations
b) Above 100, it is in MB and allocates specified Memory to the operation

As I said it is a quick fix and should be treated as quick fix only and you may need to test if it is affecting other operations performance.


--Happy Processing!!

0 comments:

Post a Comment