Controlled local SQL experiment / Synthetic data
Same answer. Fewer pages read.
My 2023 database paper collected wait statistics and proposed remedies. It never tested an intervention. For this retrospective, I ran a separate experiment on invented data to make that missing step concrete.
Give the hypothesis a fair test
The hypothesis: a narrow index covering a selective aggregate should reduce the pages the query reads. The test used SQL Server 2019, a table of 100,000 generated rows, and a status predicate matching 1,000 rows. Each row contained an integer key, status, quantity, and a 400-character filler column. The baseline had only the clustered primary key.
This exact query ran before and after the change:
SET STATISTICS IO ON;
SELECT SUM(CONVERT(bigint, Quantity)) AS quantity
FROM dbo.DemoOrders
WHERE StatusCode = 7
OPTION (MAXDOP 1);The only schema change was:
CREATE NONCLUSTERED INDEX IX_DemoOrders_Status
ON dbo.DemoOrders (StatusCode) INCLUDE (Quantity);Each variant had one warm-up followed by five serial executions, with unchanged rows and no cache clearing. Every execution returned the same sum. The read counts above were identical across each group of five runs.
STATISTICS IO counts logical page reads from the data cache separately from physical reads. All recorded runs had zero physical reads; the result demonstrates less read work in this fixture. It does not demonstrate reduced I/O waits, a particular latency improvement, or a production speedup. Microsoft’s measurement reference defines those counters.
Account for what the index costs
The new index used 1,928 KiB; the existing clustered index used 42,280 KiB, measured through sys.dm_db_partition_stats. This trial did not measure index-build time or write overhead. Inserts and relevant updates now have another structure to maintain. Microsoft’s included-column guidance describes both coverage and maintenance tradeoffs.
The fixture deliberately favours this access pattern. Before proposing the index for a real workload, I would vary selectivity and data size, inspect plans, measure writes, and run neighbouring work concurrently. The experiment supports a bounded conclusion, not a general instruction to add indexes.
Return to the wait with a better question
A wait category helps choose what to investigate. It does not select the remedy. Two explanations from my original paper needed correction:
- PAGEIOLATCH_SH concerns a page I/O latch, not a transaction shared lock. Investigate both requested I/O and storage behaviour. Microsoft’s I/O troubleshooting guide.
- ASYNC_NETWORK_IO can reflect a client consuming results slowly; it does not establish a distributed commit protocol. Microsoft’s client-consumption guidance.
I would compare wait deltas over a known interval, with the workload and user-visible symptom recorded. The useful habit is to predict what one change should affect, preserve result correctness, and then check whether the wider system benefits.
← All writing