SQL Server Memory Pool

In Microsoft® Windows NT®, Windows® 2000, Windows 95, and Windows 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.

Each instance of Microsoft SQL Server™ 2000 has an address space with two main components, each of which has several subcomponents:

• Executable code

  The number and size of the executable files and dynamic link libraries (DLLs) used by an instance of SQL Server 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:

  • Distributed queries can load an OLE DB Provider DLL on the server running the instance of SQL Server.
    
    
  • Extended stored procedures are implemented as DLLs that are loaded into the address space of the instance of SQL Server.
    
    
  • The OLE Automation system stored procedures are used to create instances of OLE Automation objects. Each class of OLE Automation object loads its own code into the address space of the instance of SQL Server.
• Memory pool

  The memory pool is the main unit of memory for an instance of SQL Server. Almost all data structures that use memory in an instance of SQL Server are allocated in the memory pool. The main types of objects allocated in the memory pool are:

  • System-level data structures

    These are data structures that hold data global to the instance, such as database descriptors and the lock table.

  • Buffer cache

    This is the pool of buffer pages into which data pages are read.

  • Procedure cache

    This is a pool of pages containing the execution plans for all Transact-SQL statements currently executing in the instance.

  • Log caches

    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 fast, robust code.

  • Connection context

    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.

• Stack space

  Windows allocates stack space for each thread started by SQL Server. The default size for the stack space is 512K.

The size of the memory pool used by an instance of SQL Server 2000 can be very dynamic, especially on computers running other applications or other instances of SQL Server. By default, SQL Server seeks to keep the amount of virtual memory allocations on the computer at 4 to 10 MB less than the physical memory. The only way an instance of SQL Server can do this is by varying the size of its address space. The only variable component in the address space for an instance of 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 an instance of 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 memory 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 2000 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.