SQL Server Performance Tuning

How Widening One Index Made a Stored Procedure 227x Faster

Updated October 29, 20252 min read

One wider index: 227x 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

227xDURATION
50xCPU
312xDISK

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.

GetNextJobTicketNumberByTeamID 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.

Before vs. after

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

GetNextJobTicketNumberByTeamID 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

2,496
Before tuning
8
After tuning

Before TuningAfter TuningImprovement
Duration*2271227x
CPU*50150x
Disk*2,4968312x
*The numbers are an average of multiple T-SQL [GetNextJobTicketNumberByTeamID] runs.

Clearly, with just a few tweaks to indexing, the query ran 227 times faster.

Disk reads dropped 312 times and CPU time dropped 50 times.

Duration, ms

227
Before tuning
1
After tuning

Why replace an index instead of adding another 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 ➜