SQL Server Performance Tuning

How We Made T-SQL Queries Run 589 Times Faster

Updated October 29, 20252 min read

Merging two indexes: 589x faster. Two thin books join into one thick book, shown with a bold arrow.

Here is what can be done during a session of SQL performance tuning.

Improvement after tuning

312xDURATION
50xCPU
227xDISK

Minimize indexing for faster SQL Server performance; excess indexes slow it down.

Problem

The stored procedure below was one of the most called on the client’s SQL Server.

GetNextWorkOrderNumberByGroupID index

The procedure was overusing resources:

  • Duration: 227 ms
  • Average CPU: 50 ms
  • Disk, number of disk reads: 2,496

The solution

Here is how we improved that:

  • Dropped the [IX_ClientID_OrderDate]
  • Added the index [IX_ClientID_OrderDate_WorkOrderNumber]

That’s it! That simple!

Before vs. after

Here's the before-and-after snapshot that showcases the efficiency gains:

GetNextWorkOrderNumberByGroupID_profiler after improvement

We took 5 separate runs to analyze after the fix:

Duration
(ms)
CPU
(ms)
Disk
(8k page reads)
BeforeAfterBeforeAfterBeforeAfter
Run 128916312,4968
Run 29913112,4968
Run 311413112,4968
Run 431416312,4968
Run 532216212,4969
AVG22715012,4968

Disk, number of reads

2496
Before tuning
8
After tuning

Before TuningAfter TuningImprovement (%)Improvement (X)
Duration*227122,700227
CPU*501500050
Disk*2,496831,200312
Overall improvement:58,900% faster589x faster
*The numbers are an average of multiple T-SQL [GetNextWorkOrderNumberByGroupID] runs.

Clearly, with just a few tweaks to indexing, the query execution time plummeted by 589 times!

Duration, ms

227
Before tuning
1
After tuning

Why do you merge two indexes into a single one?

Because indexing is not free. The fewer indexes you can get away with, the faster your SQL Server runs.

Indexes can greatly improve query performance in the SQL Server, but they may hamper the performance too.

So, 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.

See more SQL Server Performance Optimization examples here.

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 ➜