locked
SSAS with SQL Server Memory Management RRS feed

  • Question

  • Hi,

    I'm using SQL Server 2012.

    I have SQL DBA experience but have not done SSAS DBA before.

    I have a server where I am running 2 instances of SSAS with one instance of SQL Server. I know that this is not a usual implementation but I need to make sure that I configure its memory efficiently.

    It would be great to know how SQL Server instance manages memory when SSAS needs it? and how should I configure memory in each instance? if I allocate 90% memory to SQL Server then will it release some memory for SSAS when needs?

    Please share any knowledge you have that matches the scenario.

    Thanks,

    SQL DBA

    Monday, April 29, 2019 8:37 PM

Answers

  • if I allocate 90% memory to SQL Server then will it release some memory for SSAS when needs?

    No, not really. SQL and SSAS have complety different Memory Management System. SQL Server grabs as much Memory as required and as it can get; it Releases Memory only on OS preasure. So SQL and SSAS will "fight" all the time for existing Memory.

    Setup max Memory for both the realistic values so both work well; 995 isn't one.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, April 30, 2019 5:51 AM

  • It would be great to know how SQL Server instance manages memory when SSAS needs it? and how should I configure memory in each instance? if I allocate 90% memory to SQL Server then will it release some memory for SSAS when needs?

    Allocating 90% of memory to SQl Server where you have 2 SSAS services running would be a blunder. SSAS takes memory what is left after allocating to SQL Server. I would not suggest you to move with this. Say you have 64 G memory so as per you 58G should go to SQL Server and just 6 GB left for OS, AV and 2 SSAS instances. This may cause severe memory pressure on OS and SSAS.

    I would suggest you to leave at least 12-14 GB for OS and give rest to SQL Server. How much total RAM you have


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, April 30, 2019 10:32 AM

All replies

  • Hi SQL DBA,

    To be honest, there are two possible modes in SSAS.

    For multidimensional instance, the setting LowMemoryLimit would affect the allocation of memory.

    For tabular instance, the settings are VertiPaqMemoryLimit and VertiPaqPagingPolicy.

    Or you may refer to the suggestions of the following links to start your memory settings.

    SSAS MEMORY CONFIGURATIONS FOR COMMON ARCHITECTURES

    Best Regards,

    Will


    MSDN Community Support
    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 MSDNFSF@microsoft.com.

    Tuesday, April 30, 2019 2:40 AM
  • if I allocate 90% memory to SQL Server then will it release some memory for SSAS when needs?

    No, not really. SQL and SSAS have complety different Memory Management System. SQL Server grabs as much Memory as required and as it can get; it Releases Memory only on OS preasure. So SQL and SSAS will "fight" all the time for existing Memory.

    Setup max Memory for both the realistic values so both work well; 995 isn't one.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, April 30, 2019 5:51 AM
  • If you have budget, please plan to put SSAS on different server and monitoring can be done from the SQL Server by setting some DMVS as per your requirement.

    If not, then please do baseline(at OS and SQL level both using some jobs or tools) for some time and set up memory accordingly. Normally such requirements are shared by application team to setup application properly on a given architecture and that is called as NFR(Non functional Requirement Document). Might be this is being called by different name too in other organizations.


    Santosh Singh

    Tuesday, April 30, 2019 6:20 AM

  • It would be great to know how SQL Server instance manages memory when SSAS needs it? and how should I configure memory in each instance? if I allocate 90% memory to SQL Server then will it release some memory for SSAS when needs?

    Allocating 90% of memory to SQl Server where you have 2 SSAS services running would be a blunder. SSAS takes memory what is left after allocating to SQL Server. I would not suggest you to move with this. Say you have 64 G memory so as per you 58G should go to SQL Server and just 6 GB left for OS, AV and 2 SSAS instances. This may cause severe memory pressure on OS and SSAS.

    I would suggest you to leave at least 12-14 GB for OS and give rest to SQL Server. How much total RAM you have


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, April 30, 2019 10:32 AM