A block cache, also called a buffer cache, holds database pages read from disk. Queries are satisfied from cached pages; missing pages are loaded into cache first. section 1.2.1
Mistake: returning more columns or rows than necessary. Extra data fills the cache faster, releases older pages sooner, and can force more disk reads, reducing performance. section 1.2.1
SELECT
ProductName
, ProductDescription AS Description
, CASE
WHEN StockQty >= 10
THEN N'In Stock'
WHEN StockQty > 0 AND StockQty < 10
THEN CAST(StockQty AS NVARCHAR) + ' left in stock'
ELSE 'Out of stock'
END
, ProductImage
FROM dbo.Products WITH (NOLOCK)
WHERE ProductID = @ProductID
A product lookup illustrates the safer pattern: request only the fields needed for the result, while recognizing that NOLOCK affects consistency rather than bypassing the cache. section 1.2.1
Use the cache responsibly:
- Select only the data the query needs. section 1.2.1
- Treat unnecessary result data as a performance cost, not merely extra output. section 1.2.1
