SQL Server dynamically allocates and deallocates memory in a pool.
The memory pool consists of memory that is divided among the following components:
* Buffer cache
Holds the database data pages that have been read into memory.
The buffer cache usually takes up most of the memory pool.
* Connection memory
Used by each connection into SQL Server.
Connection memory consists of data structures
that keep track of each user's context and includes
cursor-positioning information, query parameter values, and stored-procedure information.
* Data structures
Consist of global information about both locks and database descriptors,
including information about lock holders, about the types of locks that are held, and about various files and filegroups.
* Log cache Used to hold log information that will be written to the transaction log.
It is also used when log information that has recently been written into the log cache is read.
The log cache improves performance of log writes.
The log cache is not the same as the buffer cache.
* Procedure cache
Used to store the execution plans for Transact-SQL (T-SQL) statements
and stored procedures when they are being executed.
Because memory allocation changes dynamically,
if dynamic memory management is allowed,
the memory pool might be constantly increasing or decreasing.
Also, the five components of the memory pool can change their individual sizes dynamically.
This capability is not configurable and is controlled by SQL Server.
For example,
if more memory is needed so that more T-SQL statements can be stored in the procedure cache,
SQL Server might take some memory from the buffer cache and use it for the procedure cache.
Source: Microsoft SQL Server 2000 Administrator's Companion Book