I’m going to tell you a secret. Index tuning is complicated, but it’s something you can become great at. You just need to practice it regularly.
Here’s that one step: stop thinking index tuning is a problem for Future You.
That’s it. Really. If you read this headline and didn’t skip the post, your job probably involves helping an application using SQL Server go faster. If that’s the case, index tuning is a problem for Present You. It’s a problem for you now, it’s a problem for you next month, and it’s still a problem the month after that.
Index tuning isn’t something you do once a year. It’s something that you need to do iteratively– that means every month. Over time, data sizes change, user activity changes, and the SQL Server optimizer changes. Each of these things mean that indexes that are best for an application will also change. As you tune indexes, your query plans will change and you’re very likely to see more opportunities to add, drop, and combine indexes emerge. Because of this, you want to do a few changes every month.
I hear a lot of reasons why people don’t tune their indexes:
“We can’t make this change until we know exactly how much it will improve, and how it will scale.” That’s an admirable objection, but if it’s your primary concern and you haven’t invested in a solution for it, you’re never going to be able to change anything. Remember, chosing not to tune indexes isn’t a completely “safe” option– as long as your data is changing, your choice not to tune indexes may degrade your performance gradually (or sometimes significantly, all at once).
“There’s no good time to change indexes.” This is sometimes true in very high transaction systems, and in SQL Servers with large tables (particularly if they use Standard Edition, which doesn’t have online index operations). However, it’s rare for there not to be a potential maintenance window once or twice a month when you can make a change. Always have a rollback plan. If you don’t start somewhere, you’ll never get the ability to tune anything.
“It’s too risky.” Changing indexes is a relatively low risk action, as long as you are in charge of the codebase. (If you’re working with a vendor’s code, you usually need to talk them into tuning indexes.) A few types of changes, such as indexing computed columns, adding filtered indexes, or using indexed views runs the risk of making data modifications fail, but “vanilla” index changes are one of the lower-risk performance tuning changes you can make in SQL Server.
“Our indexes are in perfect order.” Hmmm. Are you sure? Have you checked for any potential issues lately?
Remember– your goal isn’t to fix every indexing problem you have in your first go-round. Your goal is to get started, tune indexes regularly, and track your progress as you go. If you never get started, you’ll never get better.
Liked this post? I’ve got a 6-hour video class about tuning indexes.