SQL Server Performance

default setting for max server memory is 2147483647

Discussion in 'SQL Server 2005 General DBA Questions' started by Trev256b, Jul 14, 2011.

  1. Trev256b Member

    i have changed the max memory setting to 3076. Is this 3076MB or 3GB exactly? What does the default setting of 2147483647 mean? 2GB? Will max memory setting of 3076 actually use 3GB if I have set 'lock pages in memory', enabled AWE and set the /3gb switch in the boot.ini file (on a 32 bit windows SQL Server)?
  2. satya Moderator

    The maximum memory that is supported on a SQL depends on the edition and environment (32 or 64 bit), in your case its 32 bit and you are using correct settings see KBA http://support.microsoft.com/kb/274750 too.
    The default setting you are having is nothing but SQL instnace memory settings are dynamic.
  3. Luis Martin Moderator

    1024 * 3 = 3072 = 3GB.
  4. pyale New Member

    Somethign to be aware of: With 64-bit servers, if you have plenty of memory to spare, there is no problem leaving the SQL memory setting at the default. If your server is using close to the maximum memory, though, it is better to cap the maximum SQL memory to a fixed value that leaves several Gb free, otherwise you can frequently find that the memory is not dynamically released quickly enough to prevent swapping as the OS demands memory for itself.

    e.g. If you have a 32Gb server, and your SQL memory frequently exceeds 26-27Gb, it would be worth setting your max memory to, say, 28Gb

    Nice article here: http://www.sqlservercentral.com/blo...y-settings-for-sql-server-2005_2F00_2008.aspx

Share This Page