SQL Server Instance Architecture

Figure 40.1 illustrates the address space architecture of an instance of SQL Server 2000. When you fire up a SQL Server instance, two main areas are allocated: the code area and the memory pool area. The code area will be mostly static executable code of the SQL Server Kernel, Server Net-library DLLs, Open Data Services code, the Stack Space, and a variable code area that contains distributed query OLE DB providers, OLE Automation objects, and extended-stored procedures as they are needed by user requests .

Figure 40.1. SQL Server 2000 instance architecture.

The memory pool area of SQL Server is the most dynamically changing part of an instance. Even now, the once-static System Data Structures and User Connection Structures (Connection context) are controlled by user requests and dynamically allocate structures as they are needed.

By default, SQL Server will try to keep the amount of virtual memory allocations on the computer at 4 “10 MB less than the physical memory available.

The rest of the memory pool area is divided into Procedure Cache, Data Cache (buffer cache), and Log Cache. SQL Server is actively adjusting these for optimal performance. It wasn't that long ago that the system administrator had to do all of this manually. Many of the configurable options will directly relate to optimizing this address space.