Figure 1
illustrates the address space architecture of an instance of SQL Server
2008 R2. When you fire up a SQL Server instance, two main areas are
allocated: the code area and memory pool area. The code area is mostly
static executable code of the SQL Server kernel; SQL 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.
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. Then, there are the primary SQL
Server databases master, tempdb, msdb, and model. There is another system database called a resource database
that is present in each SQL Server instance. This read-only database
contains system objects that are included with SQL Server. System
objects are physically persisted in the resource database, but
they logically appear in the sys schema of every database and provide
the results to system-level information such as server properties (SERVERPROPERTY) and object definitions (OBJECT_DEFINITION). Of these system-wide databases, tempdb
has the most significance for performance because it is the heart of
all internal tables, indexing, sorting, grouping, and other worktable
activity for the entire SQL Server instance.
By default, SQL Server tries
to keep the amount of virtual memory allocations on a computer at 4MB
to 10MB 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 actively adjusts these for optimal
performance. Previously, the system administrator had to do all this
manually. Many of the configurable options directly relate to
optimizing this address space. There is a caching framework that
utilizes internal and external clocks to determine how the caches are
managed.
