SQL Server Internals — How the Database Engine Really Works

In short: A clustered index defines the physical order of the table: its B+ tree leaf level contains the actual data rows, so there can be only one.

• A nonclustered index is a separate B+ tree whose leaf rows contain the index key plus a pointer (the clustering key or RID) to the data row; there can be many.
• Queries needing columns not in a nonclustered index do key lookups into the clustered index.

In short: A page is the basic 8 KB unit of storage and I/O, holding rows (≈8,060 bytes max in-row data) with a header and a row offset array.

• An extent is 8 contiguous pages (64 KB); SQL Server allocates space in extents.
• Special pages (GAM, SGAM, PFS, IAM) track which extents and pages are free or belong to which object.

In short: READ UNCOMMITTED (dirty reads possible), READ COMMITTED (default;

• no dirty reads), REPEATABLE READ (rows you read can't change), SERIALIZABLE (no phantoms — range locks) plus row-versioning options READ COMMITTED SNAPSHOT (statement-level consistent versions, readers don't block writers) and SNAPSHOT (transaction-level consistent snapshot with update-conflict detection).

In short: The step-by-step strategy the optimizer chose to run a query (operators like seeks, scans, joins, sorts).

• Read it right to left following the data flow, check the most expensive operators, compare estimated vs actual row counts, and look for scans on large tables, key lookups repeated many times, sorts/hash spills, implicit conversion and missing-index warnings.
• Use SET STATISTICS IO to measure logical reads before and after a change.

Connection and TDS packets → parser (syntax) → algebrizer (binding names and types) → plan cache lookup → cost-based optimizer using statistics to choose a plan (cached) → executor runs the operators → storage engine reads pages via the buffer pool (from disk if not cached), taking locks or reading row versions per isolation level → rows streamed back to the client.

In short: Objects with a histogram (up to 200 steps) and density information about column value distributions, used by the optimizer to estimate how many rows each operation returns.

• Good estimates → good plans (right join type, memory grant, index choice).
• Stale or missing statistics lead to bad plans; they're auto-created/updated, and can be updated manually (`UPDATE STATISTICS ...
• WITH FULLSCAN`) after large data changes.

In short: SQL Server compiles a parameterised query or procedure using the parameter values of the first execution and reuses that plan for all later values.

• If data is skewed, the plan can be terrible for other values.
• Fixes: a covering index that suits all values, OPTION (RECOMPILE), OPTIMIZE FOR, splitting logic, forcing a good plan with Query Store, or SQL Server 2022's Parameter Sensitive Plan optimisation.

In short: Every modification is written to the transaction log (with an LSN) before the data page changes reach disk, and a commit waits until its log records are hardened.

• After a crash, recovery runs analysis (find dirty pages and open transactions), redo (replay logged changes since the last checkpoint) and undo (roll back uncommitted transactions), returning the database to a consistent state.

In short: A session holding locks (usually a long or open transaction, or one scanning many rows because of missing indexes) blocks others needing incompatible locks.

• Find it with sys.dm_exec_requests (blocking_session_id), sp_WhoIsActive, and the head blocker's query/plan;
• fix by shortening transactions, adding indexes, committing promptly from the app, and enabling RCSI so readers don't block writers.

In short: READ UNCOMMITTED doesn't take shared locks, so a query can read uncommitted data that later rolls back, read the same row twice or miss rows when pages split during an allocation-order scan, and still be blocked by schema locks.

• It's "fast but wrong".
• Use RCSI or SNAPSHOT for non-blocking consistent reads instead.

In short: Check Query Store for a plan change (regressed query) — often due to a statistics update, parameter sniffing with a different first value, or an index change.

• Compare old and new plans (estimated vs actual rows, seek vs scan, join types).
• Quick fix: force the good plan in Query Store; real fix: update statistics, add/adjust a covering index, or rewrite non-SARGable predicates.
• Also rule out blocking (LCK_M_* waits) and resource pressure (waits, memory, I/O).

In short: Capture the deadlock graph from the system_health Extended Events session, identify the statements, objects and lock types.

• Typical fixes: access tables in a consistent order, keep transactions short, add indexes so fewer rows are locked (especially on foreign keys and WHERE columns), use UPDLOCK for read-then-write, enable RCSI to remove reader/writer conflicts — and add retry logic for error 1205 in the application.