SQL Server Performance Tuning

How One Index Tuning Pass Made 21 Queries Up to 51x Faster

Updated December 18, 20253 min read

Index tuning: 21 queries faster. A snowplow clears a road through deep snow, and small cars follow behind it.

Result of one index tuning pass

21QUERIES 6.6X TO 51X FASTER
51xBEST QUERY (DURATION)

One index change can affect many queries at once.

So we measure the result across the whole workload.

Problem

More than 100 index changes. This server had not had much care for a while, and it needed all of them.

So the client asked a fair question: after all those changes, what got better? One before and after number for one query does not answer that.

Solution

The index work covered:

  • Adding missing indexes.
  • Dropping unused indexes.
  • Merging overlapping indexes into one.
  • Removing hypothetical indexes left behind by the Database Engine Tuning Advisor.
  • Moving the most used indexes to the fastest storage.
  • Balancing filegroups across disks.
  • Index compression.
  • Indexing foreign keys.

Every index costs you on writes. The fewer you can get away with, the faster your writes are. So we removed indexes as well as adding them.

To measure it, we compared the same hour of real workload:

  • Captured the workload with SQL Server Profiler, Microsoft's tracing tool, for 60 minutes in the middle of a business day (2 PM to 3 PM).
  • Deployed the index changes.
  • Captured the same hour again four days later.
  • Replaced parameter values with placeholders, so the same query with different values counts as one.
  • Compared average duration, CPU, reads and writes for each query.

Before vs. After

The changes touched 46 frequently run queries. The 21 that improved most ran 6.6 to 51 times faster.

A sample of those 21, by average duration:

Query (anonymized)Avg duration before (s)Avg duration after (s)Improvement
Prepared lookup on a product table3.460.0751x
Document lookup by item4.660.1532x
Order lines by order number3.290.1423x
Order inventory procedure14.100.6821x
Stock on hand by location20.451.2117x
Shipments procedure2.510.1516x
Customer address update procedure31.564.447x
Each figure is the average for that query during the one-hour capture. Query text is removed to protect the client. Improvement is calculated from the unrounded averages.

We do not add these numbers together. Each query has its own result, and the fair summary is the range: 21 queries between 6.6x and 51x faster. The rest of the 46 changed by less than that.

Final thoughts

Measure index tuning across the whole workload. The same change that speeds up one query can slow down another, so you have to check all of them.

Capturing the same hour before and after is not hard to do. And it gives your client an answer they can check.

If you change 100 indexes, show what happened to every frequent query, including the ones that barely moved.

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 ➜