SQL Server Performance

Dynamic or Max/Min Memory on SQL 7.0

Discussion in 'Performance Tuning for DBAs' started by darrenwhite, Jan 14, 2003.

  1. darrenwhite New Member

    According to the article:

    http://www.sql-server-performance.com/sql_server_configuration_settings.asp

    The default max memory size for 7.0 is 2147483647 and should be left there- to make SQL allocate RAM more efficiently, but I've changed this with the slider to '769'. (Naughty!)

    As the slider doesn't move any higher than 1024, is it OK to use SP_configure to get the memory size back to 2147483647?

    I'm running 1GB ram SQL 7.0 on Windows 2000 Server, dual 2.4GHz Xeon.

    Many thanks in advance.

    Darren
  2. bradmcgehee New Member

    If you are running a dedicated SQL Server, then leave this configuration setting to dynamic. In almost all cases, this will provide you with the optimum memory performance on the server (exluding any other factors), as SQL Server will use as muc RAM as it thinks it needs. If you find that the buffer cache hit ratio is less than 95%, then most likely your server needs more RAM.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. darrenwhite New Member

    Thanks Brad,

    My issue however, is that I had changed the 'Maximum' slider, and when I now slide it all the way to the right, it results in a 'Max Server Memory' value of 1024, rather than the default 2147483647.

    So my question is, should I set it to 2147483647 using Sp_Configure?

    The article suggests that 2147483647 is necessary in order for SQL Server to manage memory fully dynamically.

    Having made this change, can I then be confident that the server will behave as if I never touched the slider?!!

    Darren
  4. satya Moderator

    What is the physical memory value on the OS and also the edition of SQL you're running.
    SQL Standard and personal editions cannot accept more than 2GB, you have to enterprise edition to get advantage of using more than 2GB.

    2147483647 value is the max amount of memory SQL server can have, the slider value shows depending upon physical memory. Follow the suggestions referred in the article.



    Satya SKJ
  5. bradmcgehee New Member

    OK, now I understand your question. As I understand it, your server has 1GB of RAM, or 1024MB of RAM. What happened was that SQL Server's EM screen defaults to a value of 2147483647, even though this may not be correct. But when you started playing with the slider, SQL Server then figured out that you only had 1024MB of RAM, and then changed the display in EM to reflect this. Dynamic memory allocation will automatically work as it should as long as the setting "Dynamically configure SQL Server memory" is selected. And as long as you have the slider all the way to the right, which in your case is now 1024, then SQL Server will automatically use all the RAM that is available when it needs to take it.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  6. darrenwhite New Member

    Many thanks Brad.

Share This Page