Memory Myths

Memory management is one of the most confusing aspects of SQL Server administration. SQL Server documentation uses many different terms when discussing memory and sometimes uses the terms in contradictory ways. You can log more than a dozen different counters when using the Windows System Monitor for observing your SQL Server system, and those are just the counters for monitoring SQL Server—specific memory. If you start monitoring memory options at the OS level, the number of possible values triples!

In one short column, I can't give you a complete treatise on memory monitoring and management. What I can do is provide some background about SQL Server memory management, then discuss some common memory myths. I assume you're familiar with basic memory concepts, so I won't discuss memory management at the OS level.

Memory Management

In general, SQL Server manages its own memory because it has in-depth information about the relationships between data and the pages it resides on. SQL Server knows better than the OS which pages to bring into memory and when, how many pages to bring in at a time, and how long to keep them in memory.

As of SQL Server 7.0, SQL Server memory is completely dynamic. By default, the total size of memory can grow and shrink as needed. Within the total memory allocated to SQL Server, the amount of memory used for specific purposes (such as storing data pages or for procedure or query plans) is also dynamic, with space being used or released as needed.

You need to be aware of the following important terms and concepts that deal with SQL Server memory:

Buffer pool. The buffer pool is made up of 8KB buffers and can be used to store data pages or to provide memory for external memory requests.

Reservation. Requesting the future use of a memory resource is a reservation—that is, a reasonable guarantee that the resource will be available in the future.

Committed memory. Committed memory is physical memory that has been allocated to a process.

Stolen buffers. When SQL Server gets a buffer from the buffer pool, it's called stealing a buffer. If the buffer is stolen and hashed for a data page, it's considered a hashed buffer, not a stolen buffer. Stolen buffers are used to store things such as procedure cache and internal server process structures.

Target memory. Target memory is the amount of memory SQL Server tries to maintain as committed memory, based on the min and max server memory configuration values (which I discuss in the next section) and current available memory. In general, SQL Server selects a target memory value to ensure that at least 5MB of memory is free at all times. If the max server memory option has been set, it represents the target until available memory becomes less than the target. If min server memory is set to the same value as max server memory, the target is fixed at that value. Note that although SQL Server attempts to maintain 5MB of free memory, under heavy use, SQL Server might not be able to free memory quickly enough.

Memory Configuration

Although SQL Server dynamically manages memory, you can use the sp_configure system stored procedure to set four options that fine-tune SQL Server's memory use. Alternatively, you can use Enterprise Manager to set three of these options; right-click your server and choose Properties, then go to the Memory tab.

Min server memory. You can think of min server memory as a floor value. SQL Server will commit memory for its own use until it reaches the min server memory setting, then it will maintain at least this value.

Max server memory. In the same way that min server memory provides a floor, max server memory provides a ceiling. The buffer pool commits memory, if it's available and SQL Server processes need it, until it reaches the max server memory value. When SQL Server encounters external memory pressure, it can release committed buffers. To maintain a somewhat static memory configuration, set the min and max memory options to the same value. After reaching the floor, the SQL Server buffer pool remains static.

Set working set size. The working set for a process is the amount of physical memory the OS has assigned to the process. This option is a Boolean value; you can set it to either 0 or 1. When set to 1, this option limits the total amount of memory that other processes can commit. The OS will favor the SQL Server process for working-set reduction and take physical memory away from other processes first. Note that when you set an application's working set size, you're taking away physical memory from the rest of the system. This can degrade the performance of other applications and the system. It might even lead to failure of operations that require physical memory, such as creating processes or threads. (In Enterprise Manager, enable this option by selecting the box labeled Reserve physical memory for SQL Server on the SQL Server property sheet's Memory tab.)

AWE enabled (SQL Server 2000 only). Like set working set size, the Address Windowing Extensions (AWE) option is a Boolean value. When set to 1, this option lets SQL Server leverage physical memory greater than 2GB, if it's available. (Note that you can't enable this configuration option through Enterprise Manager.)

Memory Myths

Because there's so much confusion about the terms and concepts used for SQL Server memory management, many myths have taken on a life of their own and have spread through the user community. Here are some of the most commonly held misconceptions about SQL Server's memory usage.

Virtual address space equals the max memory configuration. In Windows, the total amount of virtual memory available to an application is that application's address space. For SQL Server, the maximum amount of virtual memory is 2GB unless you're using a special option in Windows 2000 Advanced Server or Windows NT Enterprise Edition. The max memory configuration value represents the maximum ceiling for SQL Server's buffer pool. Although the output of the sp_configure system stored procedure seems to indicate that you can always set the value for max server memory to 2GB, that isn't true if your machine has less than 2GB of memory. You can think of the value in the output of sp_configure (in the maximum column) as the maximum max server memory that anybody can use. Enterprise Manager's Server Properties dialog box shows the maximum value for max server memory that you can set for your machine.

Private address space growth must be due to a memory leak. Private address space is the nonshared committed address space for an application. To accommodate pages being read in from disk, SQL Server increases its private space as the buffer pool grows. After SQL Server's memory reaches the max server memory value, SQL Server private address space stabilizes somewhat.

SQL Server always releases memory under pressure. SQL Server usually adjusts the buffer pool size when other processes request memory but tries to ensure that at least 5MB of memory is available. You can use Windows System Monitor to examine the value for Memory:AvailableBytes, a key indicator of memory pressure. During heavy queries that require a large amount of work space, SQL Server might not be able to reduce the size of the pool fast enough to maintain 5MB of available memory.

If set working set size is 1, SQL Server won't be paged out, and all its memory will stay physical. If other processes put excessive pressure on SQL Server, the OS might opt to swap out the entire working set.

Increasing memory to leave (-g) will help performance. Memory to leave is virtual address space set aside to ensure that SQL Server has sufficient space for thread stacks, Extended Stored Procedures (XPROCs), COM objects, and so on. Using the -g flag when starting SQL Server can change the default value for memory to leave. You need to use the -g option only on systems where physical memory is greater than approximately 1.8GB and you're receiving error message 17803, indicating that SQL Server has run out of memory.

Min server memory is committed on startup. SQL Server doesn't commit min server memory on startup. This value acts as the floor after the buffer pool is at least as large as the min server memory setting. When experiencing extreme memory pressure, SQL Server might not be able to increase the buffer pool to this size.

Enabling AWE has no effect if your server has less than 4GB of RAM. By default, Windows is limited to 4GB of physical memory. However, by leveraging Physical Address Extensions (PAE), Win2K AS can support 8GB of memory, and Windows Datacenter Server can support 64GB. You invoke PAE by including the /PAE switch in the boot.ini file. However, each 32-bit process usually has access to only 2GB of address space. To let processes allocate more physical memory than can be addressed by the 2GB of address space, Microsoft created AWE. These extensions permit the allocation and use of up to the amount of physical memory the OS supports.

Most people use AWE in conjunction with the /PAE switch to let SQL Server take advantage of physical memory greater than 4GB. Using AWE without /PAE lets SQL Server access as much as 4GB of physical memory. With AWE enabled, SQL Server by default allocates almost all memory during startup, leaving 256MB or less free. This memory is locked and can't be paged out. Consuming all available memory can prevent other applications or SQL Server instances from starting. I strongly recommend that you use the max server memory option with AWE enabled to ensure that some memory exists for other SQL Server applications or instances.

Dismiss the Myth

Now that you've learned some new things about SQL Server's memory, you'll be in a better position to manage your SQL Server resources. Next month, I'll provide additional tips for managing memory and tell you about tools you can use for your own memory testing.