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.