Improvement after tuning
Reading from disk is the slowest operation SQL Server does.
Therefore, tuning for less disk “reads” is often the primary goal.
Problem
94 seconds. That is how long one call inside a third-party application took on average. It also read almost 14 million pages per run on average.
We could not change the application code, because the vendor owns it. So the fix had to happen on the database side.
Here’s a glance at the pre-tuning metrics:
- Duration (ms): 94,198
- CPU (ms): 51,628
- Disk (number of read operations): 13,730,174
Solution
This is what we did to fix the issue:
- Added the
FAST_FORWARDoption to the SQL cursor, which makes it read-only and forward-only. We could not remove the cursor, because the application depends on it. - Added one non-clustered index on the transaction table.
- Turned a subquery into a Common Table Expression (CTE).
Why a CTE? It split a complex query into smaller parts, so we could see which join was doing the damage.
Before vs. After
The average run went from 94 seconds to 98 milliseconds.
We ran the same call five times before and five times after the change:
| Duration before (ms) | Duration after (ms) | CPU before (ms) | CPU after (ms) | Reads before | Reads after | |
|---|---|---|---|---|---|---|
| Run 1 | 49,915 | 45 | 43,016 | 0 | 16,890,022 | 2,472 |
| Run 2 | 53,032 | 22 | 45,859 | 0 | 16,894,640 | 2,252 |
| Run 3 | 6,826 | 316 | 1,376 | 16 | 121,506 | 7,587 |
| Run 4 | 165,568 | 86 | 70,906 | 31 | 17,249,975 | 3,878 |
| Run 5 | 195,652 | 21 | 96,985 | 16 | 17,494,730 | 2,222 |
| AVG | 94,198 | 98 | 51,628 | 12.6 | 13,730,174 | 3,682 |
Here is the summary:
| Before | After | Improvement | |
|---|---|---|---|
| Duration (ms)* | 94,198 | 98 | 961x |
| CPU (ms)* | 51,628 | 12.6 | 4,097x |
| Disk (number of read operations)* | 13,730,174 | 3,682 | 3,729x |
After tuning, the call ran 961 times faster on average.
An earlier session on the same application
We had tuned this system before. In an earlier session we worked on another stored procedure in the same application, under the same limit: no code changes on the vendor side.
That time we changed the cursor to FAST_FORWARD and added an index hint to force a specific index. Across five runs, total duration dropped from 93,262 ms to 30,390 ms. That is 3.1 times faster.
That was a smaller win. But it also came from the database side alone, with the application left as it was.
Final thoughts
You can often tune a vendor application from the database side. Indexes, cursor options and query shape are usually still yours to change.
Three small changes cut disk reads 3,729x here. When a query reads less data, it needs less CPU and less time. So the call now finishes in under a tenth of a second.
If your third-party application is slow, measure what it runs on the database before you buy more hardware.



