Query / SP tuningHealthcare training and compliance company, anonymized × red9CS-0293
Six always-on stored procedures reworked, the heaviest call down from 1,178 reads to 12
The problem. A healthcare training and compliance company depended on six stored procedures that fired almost nonstop, together landing somewhere near 120,000 calls a day. The worst of them read 1,178 pages and burned 4 ms on every pass, so as a group they kept a low, constant weight on storage and left the application dragging.
What we did. We profiled the six side by side, rebuilt each query around its own access pattern, and gave every one a covering index cut to its filter and output columns. Reads and duration per call were logged before the change and again once it was in.
Red9 · Performance Impact
Disk IO removed
98x
The heaviest call went from 1,178 reads to 12.
Procedures reworked
6
All six busy procs tuned in a single pass.
Duration, per call
4x shorter
4 ms down to 1 ms, spread over roughly 120,000 runs a day.
Disk reads
1,178 → 12
98x less
Duration
4 ms → 1 ms
4x shorter
Daily calls
~120,000
unchanged
Where these come from. The 98x is the heaviest call's reads before over after, 1,178 against 12. The 4-to-1 ms saving repeats across close to 120,000 executions a day, which is where the weight lifts off the box. Both sit inside the company's own before-and-after captures.
The result. The busiest procedure now touches 12 pages in place of 1,178 and wraps in about a millisecond, with the other five easing off next to it. Over 120,000 calls a day, that lifted a steady background load off the server and settled the application down.
The technical detail
What the review turned up. All six high-frequency procedures shared one weakness, a filter with no covering index, which left the busiest scanning 1,178 pages on every single call.
What we changed (identifiers generalized for privacy):
-- Six hot procedures; worst call: 1,178 reads, 4 ms, part of ~120,000 runs/day.
CREATE NONCLUSTERED INDEX IX_course_enrollments_covering
ON dbo.[course_enrollments] (/* filter cols */) INCLUDE (/* output cols */);
-- one covering index per procedure; DROP INDEX rollback scripts provided
Tuning each procedure to its own filter dropped the heaviest from 1,178 reads to 12 and 4 ms to 1 ms, and the remaining five came down with it.
