You are the storage engine

Storage Engine Drill

Execute each statement by hand. Pick the plan, then work page by page: choose which pointer to follow, which slot to read or write, which way to walk the leaf chain, and when to stop. Every page you enter costs a logical read, and every page you change costs a write. Later levels add heaps, statistics, plan reuse, index design, joins, locks and blocking, and Peak Load is an arcade mode that puts it all under time pressure.

Level–

Round–

Points0

Mistakes0

IX reads0

PK reads0

Writes0

Tree map

Pages you have read are shaded, and pages you have changed are marked with a dot. The page you hold is outlined. The trees are miniatures with a few rows per page. A real page holds hundreds of entries.

PK_t (clustered) · key (id)

Result set

Nothing yet.

Rules of the engine

Descending: on a root or intermediate page, follow the last pointer whose separator is ≤ your search key. ∅ has no lower bound and always qualifies.
Keys compare left to right. A later column only matters when every earlier column ties. −∞ and +∞ pad a search key to reach the very start or end of a group.
Seek range: equality on the leading key columns, then at most one range on the next key column. Any other predicate is a residual: it is checked entry by entry inside the range.
No seek without the leading key column. An index that can't seek can still be scanned if it covers the query.
Covered: every column the query touches (SELECT, WHERE and ORDER BY) is in the index key, row locator or INCLUDE list.
Order for free: with equality on the leading key columns, entries come out sorted by the next key column. Read backwards along m_prevPage for DESC. Otherwise a Sort is needed, and TOP can't stop early.
UNIQUE index: once every key column is fixed by =, at most one entry matches, so stop after it.
Filtered index: it only helps when the WHERE clause guarantees its filter.
Writes: INSERT and DELETE touch every index that holds the row. UPDATE touches an index only if one of its key or INCLUDE columns changes. A key change is a delete plus an insert. DELETE leaves ghost records.
Heaps: no clustered index. Rows sit in pages in no order, and nonclustered entries hold a RID (file:page:slot) instead of id. A RID lookup reads one page. A forwarding stub costs one more.
Histogram: a value equal to a RANGE_HI_KEY is estimated from EQ_ROWS. A value strictly between two keys uses the upper step's AVG_RANGE_ROWS.
Plan reuse: a parameterised query is compiled for the first value it sees, and the cached plan is reused for later values.
Every round uses a different schema: the same shapes of table, index and query, renamed. The first column is always the clustered primary key.
Reference pages: Lock Manager Walkthrough · Key Lookup Walkthrough · Inside an 8 KB Page

Storage Engine Drill

Workload

Indexes · 0 of 3

DBA tools