SQL Server MASSIVE Index Tuning Example
Speed improved by 41,925%
We often do massive SQL index tuning change.
It usually happens, when server hasn’t had tender care for a while. And its ripe for a ton of SQL indexes changes.
What index changes do we make:
1. adding missing indexes.
2. dropping unused indexes.
3. merging several indexes into a single one. Why? because indexing is not free. The less indexes you can get away with, the faster your SQL Server run.
4. Reviewing over & under indexing.
5. Hypothetical indexes, usually we just drop them.
6. Migrating most hit indexes to the fastest storage you have on the server.
7. Creating new file groups, splitting/balancing storage workload properly through multiple disks.
8. Index compression.
9. Indexing Foreign Keys.
10. And couple other things I am probably forgetting now.
The point is that there are quite a few things that need to happen.
The challenge – How do you report on 100+ index changes with solid data?
Say we end up with 100+ indexing changes that need to get deployed.
How do you show performance that was BEFORE we made indexing changes, and what happened AFTER we made those changes?
How do you show BEFORE vs. AFTER?
This is a bit harder to answer.
Single IX change will affect SQL performance in multiple places, multiple queries, multiple stored procedures, SQLAgent jobs, SSIS packages, etc.
And clients want to know (as they should), after all the changes, WHAT CHANGED?
Here is the exact process we use to report BEFORE vs. AFTER changes.
Click the image, please.
Ignore all the columns.
Just look at the last one.
Long story short. 46 frequently run queries impacted. Total gain: 41,000%!!
And that’s only from monitoring for 1h.
If you have slow SQL Server and this is not cool, I don’t know what is!
Here are the steps how we report performance changes after changing multiple indexes
1. Capture SQL Server workload during middle of the business day.
2. Save it.
3. Aggregate it. You will need to remove parameters that are being sent to stored procedures and T-SQL calls. We used ClearTrace tool. This is your BEFORE capture. BEFORE we changed anything.
4. Deploy index changes.
5. Capture SQL workload again.
6. Save it.
7. Aggregate it. Same as step #3. This is your AFTER the change went in results.
8. Now compare two performance captures: BEFORE vs. AFTER.
9. Produce result like Excel above.
And this is how we got 41,000% improvement!
See more SQL Server Performance Optimization examples here.
