A storage engine is the Database Engine subsystem that interacts with the query executor to read and write required data. Treating it as simple infrastructure is risky: transaction isolation, locking, caching, and log durability can affect correctness, performance, and availability. section 1.2.1 section 1.5
Common mistakes become easier to avoid when the storage engine’s responsibilities are separated:
- Mistake 1 — assuming queries read directly from disk: the buffer cache supplies data pages, loading them from disk when necessary. section 1.2.1
- Mistake 2 — treating transaction behavior as automatic: the Transaction Manager, Lock Manager, isolation level, and log flushing determine consistency and durability. section 1.2.1
- Mistake 3 — using NOLOCK as a harmless performance switch: it can allow reads of rows that are never committed. section 1.2.1
A tempting product lookup
A product lookup using WITH (NOLOCK) may appear efficient, but its results can include data that a transaction never commits. Check the consistency requirement before choosing a hint or isolation level. section 1.2.1
Trace a query from its plan through the query executor, transaction components, and storage engine using the Database Engine flow. section 1.2.1
