SQL Server Internals — How the Database Engine Really Works Cheat Sheet

SQL Server Internals — How the Database Engine Really Works

The SQL Server architecture (protocol layer, relational engine, storage engine, SQLOS), the life of a query from TDS packet to result, pages, extents and allocation maps, heaps vs clustered tables and B+ tree index structure, row layout, statistics and cardinality estimation, the query optimizer and plan cache, parameter sniffing, execution plan operators and join algorithms, buffer pool and memory grants, the transaction log, write-ahead logging, checkpoints and recovery, locking, latches, isolation levels, row versioning and tempdb, deadlocks, waits and DMVs, columnstore and In-Memory OLTP.

Architecture

Protocol (TDS) → Relational engine (parse, bind, optimise, execute, plan cache) → Storage engine (access methods, buffer pool, locks, log) → SQLOS (schedulers, workers, waits).

Storage

Page 8 KB · extent 64 KB · GAM/SGAM/PFS/IAM · heap (RID) vs clustered B+ tree (rows at leaf) · nonclustered (key + locator) · INCLUDE = covering · good clustered key: narrow, unique, static, increasing.

Optimizer

Statistics (200-step histogram) → cardinality estimates → cheapest plan → plan cache · SARGable predicates · parameter sniffing fixes: index, RECOMPILE, OPTIMIZE FOR, Query Store forcing, PSP (2022).

Plans

Right to left · seek vs scan · key lookup · nested loops (small outer) · merge (sorted) · hash (large unsorted, memory) · spills · implicit conversions · estimated vs actual rows.

Transactions

WAL + LSN · commit = log hardened · checkpoint · recovery: analysis, redo, undo · ADR · recovery models SIMPLE/FULL/BULK_LOGGED · log backups in FULL.

Concurrency

Locks S/U/X/intent/schema · escalation ~5,000 locks · latches = internal · isolation RU/RC/RR/SER + RCSI/SNAPSHOT (version store) · deadlock = error 1205 → retry + same access order + short transactions.

Key waits

PAGEIOLATCH (disk reads) · LCK_M (blocking) · WRITELOG (log disk) · CXPACKET (parallelism) · SOS_SCHEDULER_YIELD (CPU) · RESOURCE_SEMAPHORE (memory grants) · ASYNC_NETWORK_IO (client).