SQL Server Performance Tuning

How We Made T-SQL Queries 6 Times Faster

Updated October 29, 20252 min read

T-SQL queries 6x faster. Ploom zips along a pavement on a kick scooter.

Improvement after tuning

6xDURATION
4xCPU
3xDISK

SQL Server tuning is crucial - it reduces CPU time, disk reads, and speeds up operations, making everything more efficient.

Problem

The slow stored procedure significantly hampered SQL Server performance.

Pre-tuning Metrics

1,881
Duration (ms)
1,025
CPU (ms)
9,849
Disk (number of reads)

Solution

Here is what we've done to make this query run faster:

  • Removed SQL function from WHERE clause (subquery)
  • Removed unused columns from temporary tables
  • Replaced two temporary tables with Common Table Expressions (CTE)

Before vs. After

The improvement was huge: the total number of disk reads was 3,157 instead of 9,849!

Disc (number of reads)

9,849
Before tuning
3,157
After tuning
Duration
(ms)
CPU
(ms)
Disk
(8k page reads)
BeforeAfterBeforeAfterBeforeAfter
Run 18054765004535,5634,471
Run 2807346-33713
Run 31,1714125-1,03414
Run 43,0095631,76651610,9584,612
Run 53,6134322,68839031,3546,678
AVG1,8812951,0254539,8493,157

Below is the detailed breakdown by metric:

Before After Improvement
Duration (ms)*1,881295~6 ( or 638%)
CPU (ms)*1,025453~2 ( or 226%)
Disk (number of reads)*9,8493,157~3 ( or 312%)
*The numbers are an average of multiple T-SQL runs

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

That’s over 6x faster!

Duration (ms)

1,881
Before tuning
295
After tuning

SQL Server functions in the WHERE clause

For better performance, avoid using functions in the WHERE clause. Here is why:

When you use functions in the WHERE clause, it makes SQL Server scan the whole table or index instead of quickly finding what it needs with an index seek, assuming an applicable index exists.

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 ➜