SQL Server Performance Tuning

How We Made T-SQL Queries Run 589x Faster

Mark Varnas
No comments

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

Improvement after tuning

312x

DURATION

50x

CPU

227x

DISK

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.

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:

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.

Article by
Mark Varnas
Founder | CEO | SQL Veteran
Hey, I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Managed SQL Server services, consulting, and emergency support from expert DBAs to improve performance, predictability, and cost.

Get started with Red9 today.

Contact us

Discover More

Discover what clients are saying about Red9

Red9 has incredible expertise both in SQL migration and performance tuning.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

Rich Staats 5 stars
Rich Staats
Cloud Engineer
MetalToad

Always willing to go an extra mile

Working with Red9 DBAs has been a pleasure. They are great team players and have an expert knowledge of SQL Server database administration. And are always willing to go the extra mile to get the project done.
5 stars
Evelyn A.
Sr. Database Administrator

Boosts server health and efficiency for enhanced customer satisfaction

Since adding Red9 to the reporting and DataWarehousing team, Red9 has done a good job coming up to speed on our environments and helping ensure we continue to meet our customer's needs. Red9 has taken ownership of our servers ensuring they remain healthy by monitoring and tuning inefficient queries.
5 stars
Andrew F.
Datawarehousing Manager
See more testimonials