SQL Server Architecture

Understanding SQL Server's internal architecture helps you design better databases, write more efficient queries, and troubleshoot performance issues. This lesson covers instances, system databases, the storage engine, and memory management.

---

Instances

An instance is a running copy of the SQL Server Database Engine:

• A single server can host multiple instances
• Each instance has its own system databases, memory, and configuration
• The default instance is accessed by server name; named instances use ServerName\InstanceName

Physical Server
├── Default Instance (MSSQLSERVER)
│   ├── master, msdb, model, tempdb
│   └── User databases
├── Named Instance (DEV01)
│   ├── master, msdb, model, tempdb
│   └── User databases
└── Named Instance (TEST01)
    ├── master, msdb, model, tempdb
    └── User databases

Tip: Named instances are useful for running different SQL Server versions on the same machine, for example during migrations.

---

System Databases

Every SQL Server instance has four system databases:

Database	Purpose
master	Stores server-level configuration, logins, linked servers, and metadata for all databases
msdb	Stores SQL Server Agent jobs, alerts, backup history, and SSIS packages
model	Template for all new databases — any objects you add to model appear in every new database
tempdb	Temporary workspace for temp tables, table variables, cursors, and sort spills

tempdb Deep Dive

tempdb is recreated every time SQL Server restarts:

• Temp tables (#local and ##global) are stored here
• Table variables use tempdb
• Sort and hash spills write to tempdb when memory is insufficient
• Snapshot isolation row versions are stored in tempdb
• Best practice: Place tempdb on the fastest storage available

---

Storage Engine

The storage engine handles how data is physically stored and retrieved:

Pages and Extents

• A page is the fundamental unit of storage — 8 KB in size
• An extent is 8 contiguous pages — 64 KB
• SQL Server reads and writes data in pages
• Every table and index stores data on pages

Page Type	Contents
Data	Table rows
Index	Index entries (B-tree nodes)
Text/Image	LOB data (varchar(max), nvarchar(max), varbinary(max))
GAM / SGAM	Allocation tracking (which extents are free or mixed)
IAM	Index Allocation Map — tracks which pages belong to a table or index
PFS	Page Free Space — tracks free space on each page

Data Files

SQL Server stores data in files: