Storage and I/O Fundamentals

On-Disk Structures

SQL Server doesn't think in rows. It thinks in pages — 8 KB blocks that are the smallest unit it will ever read or write from disk. Everything lives on pages: your table data, your index data, your system metadata, and the various internal structures that SQL Server would prefer you not think about too hard.

A page has a 96-byte header (page type, page ID, linked-list pointers to prev/next pages, the Log Sequence Number for crash recovery, and a few other bookkeeping fields), then row data growing downward from byte 96, and a slot arraygrowing upward from the last byte. The slot array is a sequence of 2-byte offsets — each entry points to the physical location of one row on the page. This lets SQL Server locate any row by slot number without scanning the page body.

Eight contiguous pages form an extent — 64 KB. Before SQL Server 2016, small tables started on mixed extents shared with other objects, moving to dedicated uniform extents after eight pages. Since SQL Server 2016, all user object allocations use uniform extents from the start (the behavior previously required by trace flag 1118). SQL Server tracks page ownership through IAM pages (Index Allocation Maps). If you have ever seen a corrupted IAM page in production, you have experienced a special kind of grief that no amount ofDBCC CHECKDB can fully process.

Sequential vs Random I/O

Everything else in SQL Server query performance comes down to this: sequential I/O is fast, random I/O is slow, and the optimizer knows it.

On a spinning disk, reading a megabyte sequentially takes a few milliseconds. Reading a megabyte of scattered pages can take hundreds of milliseconds — each random read requires the actuator arm to physically seek to the right track (seek time: 5–15 ms) and then wait for the right sector to rotate under the head (rotational latency: ~4 ms average at 7,200 RPM). Those 9–19 ms per random read add up brutally at scale.

The query optimizer knows this. When it estimates that fetching individual rows would require too many random reads, it scans the entire table instead — even if that means reading more data than the query actually needs. Scanning is sequential. Sequential is faster than targeted-but-random beyond a certain threshold. This is not a bug. This is the optimizer being more correct than you.

SSD removed seek time. NVMe made random I/O look almost sequential in latency terms. This changes the numbers but not the architecture: 10,000 random logical reads is still 10,000 buffer pool lookups. The threshold where a seek becomes a scan shifted; the logic didn't.

Buffer Pool and the Read Pipeline

SQL Server does not go to disk for every query. It maintains a buffer poolin memory — a cache of 8 KB pages indexed by database ID, file ID, and page number. On a properly sized server, a warm buffer pool means most queries never touch storage at all.

When a query needs a page:

1. SQL Server looks up the page in the buffer pool hash table.
2. Logical read: page is there. It gets pinned, the query reads it, the pin is released. Cost: memory access. No disk involved.
3. Physical read: page isn't there. SQL Server fetches it from disk, loads it into a free buffer slot, then proceeds as above. The page stays cached until SQL Server needs that slot for something else — eviction uses an LRU-K algorithm (specifically LRU-2), which tracks how recently and how often a page was referenced, so large scans can't flood the pool by displacing pages that are actually useful.

Read-ahead is the prefetch mechanism. When SQL Server detects a sequential access pattern — a table scan, an ordered index range scan — the storage engine issues asynchronous prefetch requests for pages the query will need soon. The goal is to have pages in the buffer pool before the query asks for them, converting a stall into a cache hit.

SET STATISTICS IO ON exposes the raw numbers. Learn to read it:

SET STATISTICS IO ON;
SELECT * FROM dbo.Orders WHERE customer_id = 42;

-- Table 'Orders'. Scan count 1, logical reads 8427,
--   physical reads 0, read-ahead reads 8421.

Physical reads 0 with read-ahead reads 8421: read-ahead was effective enough that every page was already in the buffer pool by the time the query thread requested it — prefetched asynchronously just ahead of the scan. The actual disk I/O happened via read-ahead; the query saw only buffer pool hits. On a genuinely warm cache you'd see read-ahead reads drop toward 0 as well.Logical reads is the metric that actually matters for tuning. Physical and read-ahead reads are cache-temperature effects that disappear once the working set is warm. Logical reads don't. 50,000 logical reads means 50,000 buffer pool lookups on every execution — that's your memory bandwidth, your CPU cache pressure, your latch contention.