none
SSAS 2012- Memory Issue when Executing Query RRS feed

  • Question

  • Hi Experts, 


    I have concern executing MDX query on SSMS- connecting to Analysis server

    Cube-Multi Dimension

    Cube Size =  1 GB
    Server RAM = 64 GB

    When I execute the Query  Which is Cross Join of all 10 Dimension of my Cube and approx 15 Calculated Measure
    I get to see an error something like "System.OutofMemoryException"

    I need to understand - When Size of Cube is hardly 1 GB and we already have Server with 64 GB then how come we face Memory Issue.

    I am assuming when MDX is executed against Cube then it consume RAM, and if number of Rows are more than it goes out of Memory ( based on above Error).
    I cannot Change the Cross Join between 10 Dimensions because this is what Client hit anytime and they do not wish to see error.

    How to over come this issue.

     
    • Edited by Rihan8585 Friday, November 16, 2018 4:44 AM edit
    Thursday, November 15, 2018 11:29 AM

All replies

  • Hello,

    First we have to clarify who throws the exception: SSAS during query execution or SSMS while showing the result.

    So where do you get the except, in query result grid as red text? Then it's Server side.

    Or as a message box in SSMS; that's what I guess? Then it's SSMS, see MS Support: "System.OutOfMemoryException" exception when you execute a query in SQL Server Management Studio


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    • Proposed as answer by Pirlo Zhang Friday, November 16, 2018 2:13 AM
    • Edited by Olaf HelperMVP Friday, November 16, 2018 11:45 AM
    Thursday, November 15, 2018 11:40 AM
  • Hi Rihan8585,

    Simply add that you can use DMV to check which object are consuming the most CPU or memory at a specific point in time on service side. For example:

    This query reports on memory consumption by object.

    Select * from $System.discover_sessions

    Regards,

    Pirlo Zhang 


    MSDN Community Support<br/> Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Friday, November 16, 2018 2:13 AM
  • Hi Olaf, 

    Thanks for your time , As asked by you 

    "First we have to clarify who throws the exception: SSAS during query execution or SSMS while sowing the result."

    I have concern executing Same MDX query Both on SSMS and at SSRS Level

    I get Similar Out of Memory Error when 

    1) MDX Query is executed on SSMS -connecting to Analysis server 

    2) Team get Same Error when same MDX is used to Created/Execute SSRS Report -Connected to Same Cube 

    I understand from your link the Limitation of SSMS but how is this Impacting Execution of SSRS report hitting Cube and throwing Same error ( My Ultimate requirement is ,Client should be able to View SSRS-Simple grid report using this MDX)

    Please assist !!
    • Edited by Rihan8585 Friday, November 16, 2018 4:51 AM edit
    Friday, November 16, 2018 4:25 AM