This trace follows the actual state transitions behind the companion Find the Data Access That Dominates Query Cost. It describes a common execution path; implementation details can vary, so keep the contract separate from the mechanism.
Step 1: Capture the query and workload
Database performance depends on how many rows and pages a query touches, how much data moves between operators, and whether the working set fits in memory. An index can reduce search work, but the full execution plan and actual workload determine whether that reduction matters.
Step 2: Inspect its execution plan
An index-only scan can avoid table lookups when the requested columns are present and visibility information permits it. A cache hit can avoid storage reads, yet an application cache may return stale data or consume substantial memory. Measure buffer reads and row counts alongside elapsed time.
Step 3: Compare estimated and actual rows
An index can reduce candidate rows but still leave many table-page reads; inspect plan and buffer evidence before adding another index.
At this point, record the state that changed and check the invariant before advancing. If the operation repeats, make clear which values persist and which are recomputed.
Step 4: Count data and round trips
Adding indexes indiscriminately slows writes and increases maintenance. Query plans can change as statistics and data distributions change. A fast isolated query may still overload the service when multiplied by high concurrency or an N+1 request pattern.
Step 5: Measure the full endpoint
An endpoint issues one query for a list and then one query for each item. Estimate query count for 80 items and describe how batching changes network round trips and result cardinality.
The trace is complete when the result satisfies the stated contract. Compare this model with the concrete runtime or system you are studying before making a performance claim.