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.
8 Comments. Leave new
Hekaton will make my indexes no problem
Sure – as long as all your servers are SQL Server 2014, probably Enterprise Edition, and your data fits in memory. Last time I looked, the limit on this was around 256GB. So, uh, yeah.
LOLWUT
Tuning indexes is probably my favorite thing to do in SQL Server! It was difficult at first, but once I started to understand more of what was going on I found it to be… fun…
And then there’s the thrill of going live with your changes. The suspense! Will things improve? Will they get worse? Will I have a job tomorrow? IDK. Execute the query!
Once added a clustered index that caused the application to crash and fail to initialise. Turns out the developers (and in this case I use that noun loosely) assumed a particular result set would always return in the same order so no ORDER BY clause was added to the query. The first value in a list box based on this result set was then always assumed to be a certain value. This value was taken directly from the list box and used in a check that allowed another module of the application to initialise. If that module failed, so did the rest of the application. So I can truthfully claim to have crashed an application by changing the indexing on a table!
This same application also ran a trigger on a line item table. The trigger was 17,000 lines long, so this application was already well in trouble. But that is another story.
Holy wow! Thanks for sharing that story.
Since order’s never guaranteed without an ‘Order By’, the application could have broken at any time for other reasons, too! If the estimated query cost ever changed, the query might start going parallel and BAM, order of the results could change.
We have a similar story of an application that did bulk loads breaking when tempdb files were added to SQL Server because ordering had been assumed, but wasn’t guaranteed. (That one’s here: https://www.brentozar.com/archive/2011/08/tempdb-multiplefiles-sort/)
I have run into a situation with a set of procedures that require a large number of covering indexes to make them run in less than 1 second. All together the space used to support this is over 100GB. One thing they all have in common is this in the where clause.
StatusID in (1, 4, 5, 6, 7, 9, 10, 15, 20, 21, 22, 23, 25, 26, 28, 29)
So I am thinking this is a good use for a Filtered index. What do you think?
I have a 1 tb db in which rebuilding indexes weekly job is scheduled after the log backup job but from past 3 weeks this job is getting failed. Can someone please help me out