none
Max Memory for Sql Server 2012 ( Standard ) + Analysis on same server

    Question

  • I know the maximum memory for SQL Server database Standard is 64 GB RAM. I have a Standard Version (2012),

    What is the maximum memory I can use for Both SQL Server Analysis and SQL Server Database on that same machine ?
    Does it use that same shared 64 Gb spaces ? or does he have his own 64 GB, thus making my maximum memory to 128 GB ?

    If I look at the processes I can see sqlservr.exe taking his max 64 GB.

    but the msmdsrv.exe  never seems to use a lot of memory, even when make a lots of request to cubes. 

    I ask to know if I would gain some performance by upgrading to the max supported memory ,

    or maybe I am actually at the max ?


    k.r.

    Wednesday, June 19, 2013 2:50 PM

Answers

  • You need to cap memory for SQL Server Database and Analysis Services individually. Set max server memory setting for SQL Server database. Set it to somewhere between 5o to 55 GB.

    http://msdn.microsoft.com/en-us/library/ms178067.aspx

    http://blogs.msdn.com/b/sqlsakthi/archive/2011/03/12/importance-of-setting-max-server-memory-in-sql-server-and-how-to-set-it.aspx

    You can refer below link to set memory for Analysis Services,

    http://msdn.microsoft.com/en-us/library/ms174514.aspx


    Thanks & Regards RAJUKIRAN L Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.

    • Proposed as answer by Tom Phillips Wednesday, June 19, 2013 4:46 PM
    • Marked as answer by karlr30 Monday, July 15, 2013 5:59 PM
    Wednesday, June 19, 2013 3:27 PM

All replies

  • You need to cap memory for SQL Server Database and Analysis Services individually. Set max server memory setting for SQL Server database. Set it to somewhere between 5o to 55 GB.

    http://msdn.microsoft.com/en-us/library/ms178067.aspx

    http://blogs.msdn.com/b/sqlsakthi/archive/2011/03/12/importance-of-setting-max-server-memory-in-sql-server-and-how-to-set-it.aspx

    You can refer below link to set memory for Analysis Services,

    http://msdn.microsoft.com/en-us/library/ms174514.aspx


    Thanks & Regards RAJUKIRAN L Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.

    • Proposed as answer by Tom Phillips Wednesday, June 19, 2013 4:46 PM
    • Marked as answer by karlr30 Monday, July 15, 2013 5:59 PM
    Wednesday, June 19, 2013 3:27 PM
  • Hi Karl,

    It might be that SSAS just doesn't feel the need to use more memory.  Just because you set /LowMemoryLimit to 65%, it doesn't mean that it will use it.  See http://richardlees.blogspot.com.au/2011/12/why-doesnt-ssas-cache-entire-cube.html

    With a default set up, when SQL Server and Analysis Services are on the same machine, SSAS will use as much as it wants without dynamically adjusting for Windows Server memory pressures.  The default setup is 65% and 80% for Analysis Services /LowMemoryLimit and /TotalMemoryLimit respectively.  (That's a percent of RAM.)  However, it will tend not to use even 65% of memory for caching data.  Generally it will only get over the 65% while processing, or query results that require memory for intermediate results during query execution.  On the other hand SQL Server does not have a memory limit by default (or a large one like your 65GB), rather, it is "listening" for Windows Server memory pressure and it will reduce memory demands when Windows is stressed and take more when SQL is busy and Windows isn't stressed.  

    I don't think SSAS is holding back on memory just because SQL Server is using 64GB.  SQL Server, in my opinion, should be configured to use a max of 64GB and a minimum of something much smaller, say 2GB.  Then SQL Server would simply return memory to Windows if SSAS, or some other process, was taking so much memory that Windows was over committed and thrashing.


    Richard

    Thursday, June 20, 2013 12:44 AM
  • Thanks for answering,

     What I would like to know is : What if I put 132 GB ram, and set DB to use his 64 GB, Set SSAS to use 64 GB and the rest to the OS.

    Will it work ? or does the 64 GB limit of the Standard version will prohibit this ? Or will  they both try to use the same 64 GB ?


    k.r.


    • Edited by karlr30 Tuesday, June 25, 2013 2:19 PM precise question
    Tuesday, June 25, 2013 1:55 PM
  • No one can answer that ?

    What I would like to know is : What if I put 132 GB ram, and set DB to use his own 64 GB, Set SSAS to use his own 64 GB and the rest to the OS.

    Will it work ? or does the 64 GB limit of the Standard version will prohibit this ? Or will  they both try to use the same 64 GB ?


    k.r.

    Wednesday, June 26, 2013 12:27 PM
  • Hi,

    SQL server service and Analysis Services are two different services which means two different .exe will be running. So both of them will use the individual memory allocated to them. In your case it will work. But do you really need 128 GB of RAM? You need to check the load on the server like the number of transactions hitting the server per sec or so.


    Thanks & Regards RAJUKIRAN L Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.

    Wednesday, June 26, 2013 12:29 PM
  • Thanks.

    We are working with huge Databases, so for the DB part, 64 Gb of cache is tiny.

    As for the SSAS, I will be experimenting with memory settings, for now even set @ 65%, I have not seen the process msmdsrv.exe memory going over 1 gb.

    But  a problem we have is the Db hitting the 64 GB barrier, and after that, we get hard fault, thus rendering all request slower (including the SSAS one)

    I have change the Max memory DB settings from 64 GB to 55 GB and will see the difference.


    k.r.

    Wednesday, June 26, 2013 1:28 PM