SQL Server Internals — How the Database Engine Really Works

SQL Server looks simple from outside — you send T-SQL and get rows — but inside it is an operating system of its own. Understanding it explains why indexes help, why a query is slow, why blocking and deadlocks happen and how data survives a crash.

Client (ADO.NET / EF / SSMS)
   │  TDS (Tabular Data Stream) over TCP 1433
┌──▼──────────────────────────────────────────────────────────┐
│ PROTOCOL LAYER (SNI) — connections, TDS packets               │
├──────────────────────────────────────────────────────────────┤
│ RELATIONAL ENGINE (query processor)                           │
│   Parser → Algebrizer (binding) → Query Optimizer → Executor   │
│   Plan cache                                                   │
├──────────────────────────────────────────────────────────────┤
│ STORAGE ENGINE                                                 │
│   Access methods (scans, seeks, B-trees, heaps)                │
│   Buffer manager (buffer pool)  ·  Transaction manager         │
│   Lock manager  ·  Log manager                                 │
├──────────────────────────────────────────────────────────────┤
│ SQLOS — schedulers, workers, memory, waits, I/O, deadlock detection │
└──────────────────────────────────────────────────────────────┘
   Data files (.mdf/.ndf, 8 KB pages)     Log file (.ldf, sequential)     tempdb

Life of a query — SELECT Name FROM dbo.Customers WHERE Id = 42:

1. Connect — the client opens a TCP connection (pooled by ADO.NET), authenticates, gets a session (SPID).
2. Send — the batch arrives as TDS packets; a worker thread on a scheduler (one per CPU core) picks it up.
3. Parse — syntax check → parse tree.
4. Algebrize (bind) — resolve names (tables, columns, types), check permissions → query tree.
5. Plan cache lookup — hash the query text; if a cached plan exists, reuse it (skip optimisation).
6. Optimize — cost-based optimizer uses statistics to estimate rows, explores alternatives (indexes, join orders and algorithms) and chooses the cheapest execution plan; plan stored in cache.
7. Execute — the executor runs plan operators; the storage engine reads needed pages through the buffer pool (from memory if cached, otherwise physical reads from disk).
8. Lock — shared locks (or row versions) protect consistency according to the isolation level.
9. Return — rows are streamed back in TDS packets; locks released per isolation rules.

For a write: the change is applied to pages in memory, the log record is written to the transaction log first (write-ahead logging), and on COMMIT the log is hardened to disk; dirty data pages are written later by checkpoint/lazy writer.

• SSMS — actual/estimated execution plans, Live Query Statistics, Activity Monitor, Query Store reports.
• DMVs — sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_stats, sys.dm_os_wait_stats, sys.dm_tran_locks, sys.dm_db_index_physical_stats, sys.dm_db_index_usage_stats, sys.dm_exec_query_plan.
• Extended Events (replaces SQL Profiler), system_health session (deadlock graphs).
• Query Store — plan history, regressions, forcing plans.
• DBCC — DBCC PAGE, DBCC IND (undocumented; learning), DBCC SHOW_STATISTICS, DBCC CHECKDB, DBCC SQLPERF(LOGSPACE).
• Community: sp_WhoIsActive, First Responder Kit (sp_Blitz, sp_BlitzIndex, sp_BlitzCache), SentryOne Plan Explorer.