Result of one index tuning pass
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 table | 3.46 | 0.07 | 51x |
| Document lookup by item | 4.66 | 0.15 | 32x |
| Order lines by order number | 3.29 | 0.14 | 23x |
| Order inventory procedure | 14.10 | 0.68 | 21x |
| Stock on hand by location | 20.45 | 1.21 | 17x |
| Shipments procedure | 2.51 | 0.15 | 16x |
| Customer address update procedure | 31.56 | 4.44 | 7x |
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.



