Storage Internals and Architecture

32.1 Pages and Extents

Page = 8 KB (8,192 bytes): a 96-byte header, data rows, and a row offset array.

Max in-row size: 8,060 bytes. Larger values go to ROW_OVERFLOW_DATA or LOB_DATA allocation units.

Extent = 8 contiguous pages (64 KB). Mixed vs uniform extents (uniform by default since 2016).

Page types: Data, Index, Text/Image, GAM, SGAM, PFS, IAM, BCM, DCM, Boot, File Header.

32.2 Allocation Structures

GAM/SGAM track extent allocation. PFS tracks page free space. IAM maps the extents belonging to an allocation unit.

DCM (differential bitmap) supports differential backups. BCM tracks bulk-changed extents.

32.3 Row Structure

Status bits, fixed-length data, null bitmap, variable-length column offset array, variable data, and a 14-byte versioning tag (when versioning is enabled).

32.4 Heaps vs Clustered Tables

Heap: no clustered index. Rows are located by RID (File:Page:Slot). Forwarded records appear after updates.

Clustered index: a B+tree with the data rows at the leaf level, ordered by the clustering key.

32.5 Transaction Log Internals

Write-Ahead Logging (WAL): log records are hardened before data pages are written.

LSN (Log Sequence Number), VLFs (Virtual Log Files). Too many VLFs slow recovery. Check them with sys.dm_db_log_info.

Log truncation and log_reuse_wait_desc (e.g., LOG_BACKUP, ACTIVE_TRANSACTION, REPLICATION, AVAILABILITY_REPLICA).

SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases;
DBCC SQLPERF(LOGSPACE);
SELECT * FROM sys.dm_db_log_space_usage;

32.6 Buffer Pool and Memory

Data pages are read into the buffer pool. Dirty pages are written by checkpoint and lazy writer.

Page Life Expectancy (PLE), buffer cache hit ratio.

Memory clerks, plan cache, memory grants (sys.dm_exec_query_memory_grants).

max server memory must be set! Leave room for the OS.

32.7 Query Processing Pipeline

Parsing: syntax check produces a parse tree.

Binding/Algebrizer: name resolution and type derivation.

Optimization: cost-based. Trivial plan → search phases 0/1/2. Uses statistics and the cardinality estimator.

Execution: iterators (row mode) or batch mode.

The plan is cached in the plan cache.

32.8 SQLOS and Scheduling

Schedulers (one per logical CPU), workers, tasks Wait states: RUNNING → SUSPENDED (waiting on a resource) → RUNNABLE (waiting for CPU)

Wait statistics are the foundation of performance troubleshooting (Module 46).

32.9 Recovery Process

Crash recovery phases: Analysis → Redo → Undo. ADR adds Logical Revert and sLog.