SQL Server Performance

Setting MAX SQL Server Memory value

Discussion in 'Getting Started' started by MikeB47, Sep 25, 2007.

  1. MikeB47 New Member

    I read your article(s) on the web at http://www.sql-server-performance.com/ I am confused by the setting of max server memory on MS SQL Server 2005 using both the Enterprise Manager GUI and sp_configure. The setting at the Enterprise Manager - Server - Properties and in TSQL sp_configure stipulates that the value entered should be in MB… however, the default setting is displayed in the following message and in the help for sp_configure : The "maximum server memory" setting, when set to the default value of 2147483647 (in MB), tells SQL Server to manage the use of memory dynamically, and if it needs it, to use as much RAM as is available (while leaving some memory for the operating system).If that value is truly (in MB), then is the default value setting actually 2147483647 x 1024 x 1024 ?If I want to “restrict” my SQL Server to use only 1GB, would I enter 1024 (in MB) in the max server memory value ? Or should I enter 1073741824 in the max server memory value ? I am only concerned and want to restrict the memory utilization as we have some other apps running on the SQL server box that are not as well-behaved as the O/S or SQL and we have had some situations where our app has been “unloaded due to memory pressure” automatically by something with unpleasant results for our users… and we have 4GB of RAM available.
    Experience so far has shown that either approach produces the desired result (does sql server check the value and "decide" the rationality of the number)but is there something that might depend on entering the value one way or the other?
    Mike Bennett
  2. bradmcgehee New Member

    If I understand the question correctly, the number is expressed in megabytes. So if you want to limit memory to 1GB, then the setting would be 1024.
    As a best practice, it is ideal if you can only run SQL Server on a single box. This prevents problems like you describe with badly behaved programs. Unfortunately, if the programs are badly behaved, there is nothing from SQL Server's point of view to resolve the problem. Reducing the amount of RAM that SQL Server uses may delay the point where you get a memory pressure error, but it won't eliminate it if the problem is a memory leak in the badly behaving program.
    Even though your server has 4GB of RAM, can the programs you are running on the server actually access all of it? I have seem many instances where the OS was not correctly configured and not all of the available RAM was not used. In addition, I have seen applications that don't know how to access large amounts of available RAM.
  3. satya Moderator

    Welcome to the forums!
    Is it recommended by anyone to set min & max memory settings, if it is a dedicated box then its better to leave the memory settings to DYNAMIC rather than playing with the values.

Share This Page