A database page is a fixed-size SQL Server storage unit containing rows or portions of rows from database objects. Queries are satisfied from the buffer cache: required pages are read from disk into memory first. Returning more columns or rows than necessary fills the cache faster, releases older pages sooner, and can increase disk reads. section 1.2.1 section 11.0
A common mistake is treating all page layouts as equivalent:
- Mistake 1—assuming a heap is organized for fast lookup. A heap stores data pages in no particular order, so many queries may read every page in the table. section 11.0
- Mistake 2—expecting a clustered index to leave pages independent of the key. Its B-tree leaf level is the table’s data pages, ordered by the clustered key; only one clustered index can exist. section 11.0
- Mistake 3—confusing nonclustered index leaves with table data. They contain pointers to heap or clustered-index pages, allowing multiple nonclustered indexes. section 11.0
Marketing product lookup
A product query that selects only the needed fields reduces unnecessary buffer-cache pressure. However, adding NOLOCK can permit rows that are never committed to be read, so performance caution does not remove consistency risk. section 1.2.1
Figure 11.1 A heap stores pages in no particular order
Use the heap diagram to see why unordered data pages can require broad page reads. section 11.0
Figure 11.2 A clustered index creates a B-tree and orders data pages
Use the clustered-index diagram to connect the B-tree leaf level with the table’s ordered data pages. section 11.0
