Index tuningHealthcare education platform, anonymized × red9CS-0216
A query on a learning platform, from 283 ms to a third of a millisecond
The problem. A healthcare education software platform had one query parked on a hot path. Every run took about 282.71 ms, spent 241.26 ms of CPU, and read 54,265 pages, and it fired often enough to keep the box busy on that single statement for hours a day.
What we did. We captured the plan and Query Store stats, then added a covering index matched to the query's filter and output columns, so the engine seeks a few pages instead of scanning the table. We logged duration, CPU, and reads on both sides.
Red9 · Performance Impact
SQL capacity reclaimed
~3 hrs / day
Handed back on one tuned query, on the same hardware.
Duration, per call
974x
282.71 ms down to 0.29 ms.
CPU per call
846x less
241.26 ms down to 0.29 ms, capacity back to the workload.
Duration
282.71 ms → 0.29 ms
974x shorter
CPU per call
241.26 ms → 0.29 ms
846x less
Disk reads
54,265 → 158
342x less
Where the numbers come from. Each multiple is the old figure divided by the tuned one, so 282.71 ms against 0.29 ms lands near 974x and 54,265 reads against 158 is about 342x. The roughly 3 hours a day is the query's daily run count times the time trimmed off each pass. All of it traces to the platform's own before-and-after captures.
The result. The query now returns in under a third of a millisecond, spends almost no CPU, and reads 158 pages instead of 54,265. Across its daily volume, that hands the platform back close to 3 hours of SQL processing a day, with no new hardware.
The technical detail
What the review found. The query filtered a large table with no supporting index, so every call scanned it, about 54,265 reads, and burned 241 ms of CPU.
What we changed (names masked for privacy):
-- Hot query scanned the table: 54,265 reads, ~283 ms, ~241 ms CPU per call.
CREATE NONCLUSTERED INDEX IX_exam_items_covering
ON dbo.[exam_items] (/* filter cols */) INCLUDE (/* output cols */);
-- query rewritten to seek the index; DROP INDEX rollback provided
The covering index turned the scan into a seek: reads fell from 54,265 to 158, duration from 282.71 ms to 0.29 ms, and CPU from 241.26 ms to 0.29 ms.
