Index tuningIndustrial equipment manufacturer, anonymized × red9CS-0112
Two hot queries retuned, and the heaviest dropped from 9,245 reads to 6
The problem. An industrial equipment manufacturer running on one production database had two queries leaning hard on it. The heaviest read 9,245 pages on every run, and a second query ran about 80,000 times a day, so between them they kept steady pressure on storage and made staff wait on results that should have come back instantly.
What we did. We captured each query's plan and read profile, then built covering indexes matched to their filter and output columns, so the engine seeks a handful of pages instead of scanning the whole table.
Red9 · Performance Impact
Disk reads removed
1,540x
9,245 logical reads per run down to 6, on the heaviest query.
High-volume query
104 → 22 ms
Run about 80,000 times a day. Reads fell from 553 to 4.
SQL capacity reclaimed
~2 hrs / day
Across the two queries, on the same hardware.
Disk reads, heaviest
9,245 → 6
1,540x less
Duration, busiest
104 ms → 22 ms
~5x shorter
Executions, busiest
~80,000 / day
unchanged
Disk reads
heaviest query, per run
How the math works. Each multiple is the old number over the new one, so 9,245 reads divided by 6 lands near 1,540x, and 104 ms over 22 ms is close to 5x. The ~2 hours reclaimed a day is the time trimmed off both queries across their daily run counts. Every number here was pulled from the client's own measurements on both sides of the work.
The result. The heaviest query now touches 6 pages instead of 9,245, and the 80,000-a-day query returns in about 22 ms on 4 reads. The IO they used to burn is off the instance, and the manufacturer gets back roughly 2 hours of SQL work every day.
The technical detail
What the review found. Both queries filtered large tables with no supporting index, so each call scanned end to end, about 9,245 reads on the heaviest and 553 on the one run 80,000 times a day.
What we changed (identifiers generalized for privacy):
-- Two hot queries scanned their tables: 9,245 and 553 logical reads per run.
CREATE NONCLUSTERED INDEX IX_orders_heavy_covering
ON dbo.[work_orders] (/* filter cols */) INCLUDE (/* output cols */);
CREATE NONCLUSTERED INDEX IX_lookup_hot_covering
ON dbo.[lookup_table] (/* filter cols */) INCLUDE (/* output cols */);
-- rollback: DROP INDEX scripts provided for each
The covering indexes turned both scans into seeks: the heaviest query fell from 9,245 reads to 6, and the 80,000-a-day query from 553 reads and 104 ms to 4 reads and 22 ms.