locked
memory RRS feed

  • Question

  • Hi all,

    i have 16GB memory. how much memory i can configure for sql server?

    Saturday, June 29, 2013 8:56 AM

Answers

All replies

  • Hi ,

    Setting max memory depends on 32 and 64 it arch.You should test memory requirement by DB using perfmon counters.Generallyfor start up i use 70:30 ration 70% for sql and 30 % for OS.Then configure perfmon to adjust accordingly use below counters to benchmark

    SQL Server:Buffer Manager\Page Life Expectancy
    SQL Server:Buffer Manager\Page reads/sec
    Physical Disk\Disk Reads/sec

    Please read below links especially by Jonathan..

    http://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/

    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


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Proposed as answer by Fanny Liu Thursday, July 4, 2013 11:52 AM
    • Marked as answer by Fanny Liu Tuesday, July 9, 2013 1:44 AM
    Saturday, June 29, 2013 9:21 AM
  • Hi,

    Are you running only SQL server on the machine? If yes, then you can use 10 to 12 GB for SQL. If you are using anyother application on the same machine along with SQL server then you need to consider the memory for that application as well along with the memory for OS.


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

    Saturday, June 29, 2013 11:54 AM
  • If you are in windows 2008+ with all service packs installed you don't have to configure max server memory for sqlserver .More details in http://mssqlwiki.com/2013/04/22/max-server-memory-do-i-need-to-configure/

    If you have decided to configure for any of the reason mentioned in above blog then follow the "how to configure max server memory" section in above blog.

    @Shanky,@RAJUKIRAN  If we leave 30% for OS (or) 4 to 6 GB for OS then we are not doing anything other than wasting the physical memory available in the system.


    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem


    Sunday, June 30, 2013 4:37 AM
  • Hi Karthick,

    I think Setting the max server memory around 10 - 12 GB will not only leave 4 to 6 GB for OS I think it will used by SQL server as well for the non BPool memory. As per your blog

    http://blogs.msdn.com/b/karthick_pk/archive/2013/03/16/sql-server-memory.aspx

    “Max Server Memory” limits only Bpool, hence SQL Server memory usage will be greater than “Max server memory”

    If your operating system is windows2003 (Windows2008 is your call) make sure you cap the SQL Server MAX Server Memory after considering the memory required by other applications, Operating system, Drivers , SQL Server Non- Bpool allocations etc.

    Correct me If I'm wrong.


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

    Sunday, June 30, 2013 5:46 AM
  • Start with 8GB or even 12 GB... But you need to test it first.

    http://mssqlwiki.com/2013/04/22/max-server-memory-do-i-need-to-configure/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Proposed as answer by Fanny Liu Thursday, July 4, 2013 11:52 AM
    • Marked as answer by Fanny Liu Tuesday, July 9, 2013 1:44 AM
    Sunday, June 30, 2013 7:07 AM
    Answerer
  • @Shanky,@RAJUKIRAN  If we leave 30% for OS (or) 4 to 6 GB for OS then we are not doing anything other than wasting the physical memory available in the system.

    Hi Karthick,

    I seems u have not gone thru my comment properly i said as a startup u can start with figure of 70:30 and then by looking at perfmon parameters u can reach at some logical figure which will suit ur requirement..

    Also in think what u suggested for not setting MAX SERVER MEM applies for SQL 2012 for 2008/2008 R2 setting MAX memory is still required.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Sunday, June 30, 2013 7:59 AM
  • @Shanky,@RAJUKIRAN  If we leave 30% for OS (or) 4 to 6 GB for OS then we are not doing anything other than wasting the physical memory available in the system.

    Hi Karthick,

    I seems u have not gone thru my comment properly i said as a startup u can start with figure of 70:30 and then by looking at perfmon parameters u can reach at some logical figure which will suit ur requirement..

    Also in think what u suggested for not setting MAX SERVER MEM applies for SQL 2012 for 2008/2008 R2 setting MAX memory is still required.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    In every aspect setting 70:30 is very bad idea, Instead default is ideal for most of the environment. What I meant was not SQL Server version. If you are in windows 2008 or higher with all service packs we can leave it default.

    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Sunday, June 30, 2013 8:22 AM

  • If your operating system is windows2003 (Windows2008 is your call) make sure you cap the SQL Server MAX Server Memory after considering the memory required by other applications, Operating system, Drivers , SQL Server Non- Bpool allocations etc.

    Correct me If I'm wrong.


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

    Rajukiran,

    Non-Bpool allocations wont be that large read "How to set correct value for SQL Server Max server memory?" section of  http://mssqlwiki.com/2013/04/22/max-server-memory-do-i-need-to-configure/ .


    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Sunday, June 30, 2013 8:59 AM
  • >> In every aspect setting 70:30 is very bad idea,

    I seriously dont agree to ur comment...i have tried this in my environment as startup and reached to good value using perfmon parameters and there was no issue at all...


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Sunday, June 30, 2013 9:01 AM
  • >> In every aspect setting 70:30 is very bad idea,

    I seriously dont agree to ur comment...i have tried this in my environment as startup and reached to good value using perfmon parameters and there was no issue at all...


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    It might be working because your system might be having more memory than what is required but that is not the case in most of the environments.



    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Sunday, June 30, 2013 1:28 PM
  • >>>>If you are in windows 2008+ with all service packs installed you don't have to configure max server memory for >>>sqlserver

    I did not know that.... Thanks..


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, July 1, 2013 5:16 AM
    Answerer