In Microsoft® Windows NT® and Microsoft Windows® 95/98, the total amount of virtual memory available to an application forms the set of valid memory addresses for the application. The total virtual memory allocation for an application is known as its address space.
The Microsoft SQL Server™ address space has two main components, each of which has several subcomponents:
The number and size of the executable files and dynamic link libraries (DLLs) used by a SQL Server installation varies over time. In addition to the executable files and DLLs used by Open Data Services, the SQL Server engine, and server Net-Libraries, the following components load in their own DLLs, and these DLLs can allocate memory themselves:
The memory pool is the main unit of memory for SQL Server. Almost all data structures that use memory in SQL Server are allocated in the memory pool. The main types of objects allocated in the memory pool are:
These are data structures that hold data global to SQL Server, such as database descriptors and the lock table.
This is the pool of buffer pages into which data pages are read.
This is a pool of pages containing the execution plans for all currently executing Transact-SQL statements.
Each log has a cache of buffer pages used to read and write log pages. The log caches are managed separately from the buffer cache to reduce the synchronization between log and data buffers. This results in faster, more robust code in SQL Server version 7.0.
Each connection has a set of data structures that record the current state of the connection. These data structures hold items such as parameter values for queries and stored procedures, cursor positioning information, and tables currently being referenced.
The size of the memory pool can be very dynamic, especially on computers running other applications. By default, SQL Server seeks to keep the amount of virtual memory allocations on the computer at 5 MB less than the physical memory. The only way SQL Server can do this is by varying the size of its address space. The only variable component in the SQL Server address space controlled by SQL Server is the memory pool. The other variable components in the SQL Server address space, such as the number and size of OLE DB providers, OLE Automation objects, and extended stored procedures, are all controlled by application requests. If an application executes a distributed query, SQL Server must load the associated OLE DB provider. This means that if a SQL Server component is loaded, or another application starts up, the only mechanism SQL Server can use to release the memory needed by the new component or application is to reduce the size of the memory pool. SQL Server administrators can set limits on how much the size of the buffer pool varies through the min server memory and max server memory configuration options.
The regions within the memory pool are also highly dynamic. The SQL Server code constantly adjusts the amounts of the memory pool assigned to the various areas to optimize performance. Within the memory pool, the areas used to store connection context and system data structures are controlled by user requests. As new connections are made, SQL Server has to allocate data structures to store their context. As new databases are defined, SQL Server has to allocate data structures to define the attributes of the database. As tables and views are referenced, SQL Server allocates data structures describing their structure and attributes. This leaves the buffer cache, procedure cache, and log caches as the memory units whose size is controlled by SQL Server. SQL Server adjusts the sizes of these areas dynamically as needed to optimize performance.
For more information about the sizes of the various system and connection context data structures, see Memory Used by SQL Server Objects Specifications. SQL Server 7.0 is very efficient in the way it stores the context and state information for each connection, typically using less than 24 KB for each connection.