SQL Server Performance Tuning

How We Made a Third-Party App's Query 961 Times Faster

Updated January 21, 20263 min read

A third-party app’s query, 961x faster. Ploom tightens a pipe joint with a wrench beside a padlocked metal box.

Improvement after tuning

961xDURATION
4,097xCPU
3,729xDISK

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_FORWARD option 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 beforeReads after
Run 149,9154543,016016,890,0222,472
Run 253,0322245,859016,894,6402,252
Run 36,8263161,37616121,5067,587
Run 4165,5688670,9063117,249,9753,878
Run 5195,6522196,9851617,494,7302,222
AVG94,1989851,62812.613,730,1743,682

Here is the summary:

BeforeAfterImprovement
Duration (ms)*94,19898961x
CPU (ms)*51,62812.64,097x
Disk (number of read operations)*13,730,1743,6823,729x
*The numbers are an average of multiple T-SQL runs, captured with SQL Server Profiler, Microsoft's tracing tool.

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.

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜