SQL Server Performance Tuning

How We Made T-SQL Queries 3 Times Faster

Updated October 29, 20252 min read

One new index, 3x faster. Ploom stands beside a chunky book with one red bookmark tab sticking out of its pages.

Improvement after tuning

3xDURATION
3xCPU
11xDISK

Reading from disk is the slowest operation SQL Server does.

Therefore, tuning for less disk “reads” is often the primary goal.

Problem

The frequently executed stored procedure select_scInvoicesCredits is performing slower than expected.

Pre-tuning Metrics

2,464
Duration (ms)
2,469
CPU (ms)
118,566
Disk (number of reads)

Solution

To fix the issue we added an additional index IX to tblRcptitem.

Before vs. After

The improvement was huge: the total number of disk reads was 10,785 instead of 118,566!

Pre-tuning Metrics

118,566
Before tuning
10,785
After tuning

Below is the detailed breakdown by metric:

Before After Improvement
Duration (ms)*2,462918~3 ( or 268%)
CPU (ms)*2,469922~3 ( or 268%)
Disk (number of reads)*118,56610,785~11 ( or 1099%)
*The numbers are an average of multiple T-SQL runs

After our tuning, the stored procedure became significantly faster, showing an overall improvement of 268%!

That’s almost 3x faster!

Duration (ms)

2,462BEFORE
918AFTER

Final thoughts

Adding indexes can really speed up your SQL Server queries, but there's a catch - they might also slow things down if not used wisely.

In today's case, an additional index helped us slash the overall duration of the stored procedure by nearly 3x - an outstanding outcome!

But it’s very important to understand your overall workload and find a good balance between making your queries efficient and not paying too much for that.

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 ➜