SQL Server 2008 Instance Architecture

10/11/2010 2:19:14 PM

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.

Figure 1. The SQL Server 2008 R2 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. 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.

Video tutorials
- How To Install Windows 8

- How To Install Windows Server 2012

- How To Install Windows Server 2012 On VirtualBox

- How To Disable Windows 8 Metro UI

- How To Install Windows Store Apps From Windows 8 Classic Desktop

- How To Disable Windows Update in Windows 8

- How To Disable Windows 8 Metro UI

- How To Add Widgets To Windows 8 Lock Screen

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010
programming4us programming4us