SQL Server 2005 memory configuration

Leave a comment (0) Go to comments

Some notes on memory configuration management for SQL server 2005 (32bit and 64bit). These are  guidelines, always perform an exhaustive analysis prior to making configuration changes.

32 BIT SQL Server 2005
Switches can be added to the Boot.ini file – first , some terminology.

/PAE – this switch allows OS to access physical memory beyond 4GB. On Windows Enterprise and Datacenter Editions..
/3 GB – Forces the OS to reserve only 1GB and allow applications (e.g SQL Server ) to use the remainder

Some guidelines on what switches to use , & under what circumstances

  1. If memory <= 4GB – there is nothing to configure (except /3GB switch in boot.ini).
  2. If there is 4Gb – 16 GB physical memory – use /PAE and /3GB switch
  3. Quite often a SQL server will co exist with other applications , as well as SQL Server. In those circumstances:
    1. don’t enable /3GB switch or
    2. limit the SQL Server max Memory

    An example via command line is found below. Although you can make the same change via the SQL server Management Studio (SSMS) GUI

    SP_CONFIGURE ‘max server memory’, 4096
    RECONFIGURE
    GO

  4. If the version is SQL Server 2005 Standard\Enterprise and physical memory is greater than 4GB – enable AWE. This needs to be associated with Lock Pages in Memory & Max server
  5. memory.


  6. Don’t enable AWE for for servers with less than 4GB
  7. If there is greater  16 GB – use /PAE only . Because OS needs more than 1GB to manage allocation greater than 16GB

Also worth mentioning SQL Server 2000 Enterprise Edition, as there are still a significant amount of 2000 version SQL Servers in Production

      • 4GB physical memory:  /3GB (don’t use AWE)
      • 8GB physical memory:  /3GB /PAE
      • 16GB physical memory:  /3GB /PAE
      • 16GB + physical memory:  /PAE

64 BIT SQL Server 2005

  1. /3GB and /PAE switch is not required
  2. AWE not required on 64 bit
  3. Lock Pages in Memory.yes or no ?  This will depend on different scenarios. The main benefit of turning it on is that the buffer pool buffer doesn’t get paged out.
  4. Set max server memory (leave 2GB for OS).Also consider other apps: Backup , antivirus
  5. SQL Server 2005 Standard  or Enterprise Editions – support for use of all available physical memory.Be careful! consider OS and other applications
    • SQL Server IA64 – using Windows Server  Standard 2003 or 2008 – supports up to 1TB physical memory
    • SQL Server IA64 – using Windows Enterprise or  Data Center  2003 or 2008    – supports up to  2TB physical memory
    • SQL Server x64 – using Windows Server Standard or Web  2003/2008 – supports up to 32GB physical memory
    • SQL Server x64 – using Windows Enterprise or  Data Center  2003/2008    – supports up to  2TB physical memory
EOF - SQL Server 2005 memory configuration, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.