Professional Services

Subscribe To This Site

Memory Management in SQL Server 2005

The configuration of Memory in SQL Server 2005 is similar in many ways to that of its predecessor SQL Server 2000. This article will cover the configuration options and topics that an Administrator can use. If RAM is configured correctly then the performance of your SQL Server 2005 database can greatly improve. This article will discuss the management of the buffer cache and SQL Server memory allocation. This should allow you to then make informed choices regarding your own configuration.

Enter your E-mail Address
Enter your First Name (optional)

Don't worry -- your e-mail address is totally secure.
I promise to use it only to send you GRE Newsletter.

Buffer Cache

When we talk about caching data we are referring to the process of storing data from disk in memory. When the data is read from disk and stored in memory that data �gets cached�. Data access from memory is much faster than data access direct from disk and as such you want SQL Server to have enough memory to achieve a high cache hit ratio.

The SQL Server buffer cache is the largest in SQL Server memory and it stores and data and index pages. Each instance of SQL Server maintains a linked list (called the free list) which contains the address of pages in the buffer cache that are available (free) to store data. When SQL Server starts pages are allocated for buffer cache and they are free for use. As threads begin to access data SQL Server reads pages from physical disk which is then stored in the first free page in the list. If that thread or another must read or modify the same page it can do so from the buffer cache. This reduces I/O overhead on the physical disk and thus enhances the performance of the operation.

Header information is stored on each page in the buffer. It contains a reference counter and indicates whether the page is �dirty�. A �dirty page� is described as a page that has been modified in the buffer cache but has not yet been written back to disk. The reference counter is used to collate information on data access, an algorithm is used that decides what data to keep in the buffer cache, and the least frequently used is removed. Data in the cache that has been selected from removal if that page has been modified it is written back to disk, other wise data that has not been modified is discarded, and the page in the buffer cache is freed.

Lazy Writer Process

SQL Server uses two built-in mechanisms to scan the buffer cache and write out dirty pages, the lazy writer process and the checkpoint process. The lazy writer checks that the free buffer list does not fall below a specific size. If the free list falls below that size the lazy writer scans the cache and claim back unused pages, the limit depends on the size of the buffer cache. In windows 2000 and 2003 most of this work is done by the individual threads so the Lazy Writer does not have that much work to do, unless the system is very I/O intensive the lazy writer is needed to help maintain the free list.

Checkpoint Process

The checkpoint process also writes out modified pages to disk but does not free those pages in the buffer. The SQL Server check point operation synchronises the physical data with the state of the buffer cache by writing out all modified pages of the buffer cache to disk. The check point forces any pending transaction log records in the log buffer to be written to the log file thus ensuring a permanent copy of the data on disk when the checkpoint completes. The checkpoint process is used to help speed up the recovery process as the number of transactions that must be rolled forward is minimised. The reduction in time taken to recover does come at some cost to performance. The check point process incurs some overhead because it may perform a large number of writes to disk. These writes could potentially slow user response times. Systems that experience heavy data modification are candidates for this overhead.

The check point operation involves a number of steps:

  • Writes out all dirty data to disk.
  • Writes a list of outstanding, active transactions to the transaction log
  • Storing check point records in the log
Checkpoints occur per database, so if you are connected to the master database and run a checkpoint manually then only the master database is affected. The SQL Server automatic checkpoint then a check point is run on all databases.

Checkpoints occur when:

  • You issue a manual check point command, a checkpoint is the run against the database in use
  • SQL Server is shutdown. If the checkpoint is skipped (SHUTDOWN WITH NOWAIT) the restart will take much longer as mentioned above
  • ALTER DATABASE is used to add or remove a database file
  • Changing the recovery model from bulk-logged to full or full to simple recovery model.
  • Before a database backup
  • If your database is in full or bulk logged recover mode checkpoint are run periodically as specified by the recovery interval server setting
  • In simple recovery checkpoints are run when the log becomes 70% full or based on the recovery interval setting, which ever comes first.

    Recovery Interval � the checkpoint interval which is the time between the start of consecutive checkpoints is determined by the recovery interval and the number of records in the transaction log. The recovery interval is set for an entire instance of SQL Server. The value represents the number of minutes that you choose to allow SQL server for automatic recovery. SQL server uses an algorithm to determine when to perform the next check point. The algorithm will create checkpoint to ensure that in the event of system failure the recovery will not take longer than the time specified in your recovery interval.

    SQL Server Memory Allocation

    Memory management in SL Server 2005 requires very little, if any, user intervention and by default memory is allocated and de-allocated dynamically by SQL Server as needed for optimal performance according to the amount of physical memory available. Although this dynamic behaviours can be overridden if you so wish.

    Dynamic Memory Allocation

    In SQL Server 2005 memory allocation is by default dynamic, even when AWE is enabled. AWE is applicable on 32 bit operating systems The AWE (Advanced Windowing Extensions) API allows applications to access more than 4GB of RAM. The exception to this is when running SQL Server 2005 on windows 2000 32 bit operating system with AWE enabled; in this case memory allocation is static. AWE support is only available for STANDARD, ENTERPRISE and DEVELOPER editions of SQL Server.

    SQL server dynamically manages memory based on either the default memory setting or a setting that you specify. Dynamic memory management mean SQL Server automatically acquires and released memory as necessary. At start-up SQL Server only acquires the memory it needs at that point, as users connect and workload hits the server SQL Server will acquire more memory as necessary. SQL Server can also de-allocate memory freeing it for other applications to use however if no other application is requesting memory SQL Server will maintain its memory pool even if there are unused pages. Memory is only de-allocated if needed by another process.

    SQL server maintains it virtual memory space so it is always less than the physical memory space. This prevents excessive paging, some minimal paging is normal. By maintaining this situation SQL Server can have the largest memory available while preventing SQL Server pages from swapping to the page file on disk. The page file should not be used for SQL Server pages.

    With dynamic memory allocation, if additional applications running on the same machine as SQL Server require memory, SQL Server will release memory for them from its memory pool. Thus other applications might attempt to �Steal� memory from SQL Server. It is best practice to dedicate a system to SQL Server and not run other applications alongside it.

    Static Memory Allocation

    There are two cases in which SQL Server maintains a static amount of memory:
    • As mentioned earlier when SQL Server 2005 32 bit, Enterprise, Standard or Developer is run on Windows 2000 32 bit operating system with AWE enabled.
    • The other occurs when the memory settings (Min and Max values) are set to the same value.
    With Static memory allocation once the maximum amount of memory allocated to SQL Server is reached it will never release the memory. Even if another process needs it. If there is not enough memory to support these other applications then they are likely to experience excessive paging. This highlights the point made above regarding the need for a dedicated system for your SQL Server.

    Min and Max Server Memory

    There are three options in SQL Server that allow you to configure memory settings:

    1. Min Server Memory
    2. Max Server memory
    3. AWE-Enabled

    These are all advanced options, to view them using SP_CONFIGURE you must set SHOW ADVANCED OPTIONS to and reconfigure

    Sp_configure �show advanced options�, 1;RECONFIGURE;

    To enable the AWE Enabled setting you must the awe-enabled option to 1 (1 = enabled, 0 = disabled, 0 is the default)

    SP_CONFIGURE �awe enabled�, 1;

    You will need to restart SQL Server for the awe-enabled option to take affect.

    To achieve dynamic memory allocation you can leave the default settings for both min and max server memory, this will allow SQL Server to acquire as much memory as it needs or you can change the default values to other limited. SQL Server dynamically acquires and releases memory between then amount specified in the Min and Max settings.

    To ensure that SQL Server always has 512 megabytes if memory set the min server memory to 512:

    SP_CONFIGURE �min server memory�, 512;RECONFIGURE;

    The min option will ensure that SQL Server always has a certain amount of memory to use, thus preventing other applications from �stealing� all the memory. The Max setting can be used to ensure that memory is available for other applications and they don�t have to wait for SQL Server to release it. As an example you may want to limit SQL Server to use 1GB of memory you can configure this setting as follows:

    SP_CONFIGURE �Max Server Memory�, 1024;RECONFIGURE;

    The min and max options only require you to RECONFIGURE you do not need to restart SQL Server.

    Microsoft recommends that you allow SQL Server to configure it memory usage dynamically by leaving the min and max setting as defaults, which is ideal if you have dedicated server to SQL Server but not if you budgets are tight and you have utilise all your hardware. Just ensure you don�t cause excessive paging by configuring these values incorrectly.

    To force SQL Server to use a fixed amount of memory set both the Min and Max values to be the same. Again avoid excessive paging by not setting the fixed memory size too large for your system.


    This article has discussed how SQL Server utilises memory to enhance performance and also discussed some of the options a DBA may need to adjust so SQL Server can work in relative harmony with other applications. Microsoft�s best practice suggests a dedicated a server for your SQL Server system and I tend to agree with them but if this is not an option for you configure the SQL Server memory allocation with utmost care to minimise the impact on performance.

    Article copyrighted by www.sqldruid.com, source: Memory Management in SQL Server 2005

    footer for Memory page