“How do you weigh the risk vs the reward of your database design decisions?” asks my old friend Pat Wright. And one of the examples he gives is around indexes. I like indexes, so let’s talk about some of the choices when you’re thinking about adding a new index. And for the sake of simplicity, a new nonclustered rowstore index.
A new index might make an expensive query be much faster. Users might feel like the application has become much snappier. But a new index is not free.
It feels like I always hear people saying there shouldn’t be too many indexes on a table, and yet third-party systems often come with a single-column index on just about every column, to go with an identity column clustered primary key. A quick check will show they’re rarely used for anything except updates. Some might have seeks, but I’d almost guarantee they will have required lookups if they’re single-column indexes. (Side note: sys.dm_db_index_usage_stats contains information about lookups, but as lookups are always performed on the clustered index or heap, that’s where those stats are. I’d much rather have a stat to show how often an index caused a lookup because of missing columns. I should see if there’s a feedback item for that.)
Overlapping indexes are a different matter. I say “overlapping” rather than “duplicate”, because an exact duplicate can often be removed. It’ll cause you pain if there’s a query that uses a hint for that index, but otherwise, an exact duplicate is unnecessary. Certainly I wouldn’t want to create an exact duplicate – I’d want to figure out why I had been tempted to create one. Why isn’t that other index being used for this expensive query, for example? Chances are it’s not actually a duplicate.
An overlapping index, such as one on (Col7,Col8) compared to one on (Col7,Col8,Col9), isn’t the same as a duplicate because of the ordering. Remember that the key columns in each nonunique nonclustered index actually end with the clustered keys, so these two indexes might really be (Col7,Col8,Col1) and (Col7,Col8,Col9,Col1). If that first one is being used to filter on Col7 and Col8 and then leverage the Col1 order to perform a Merge Join on some other set of data, then adding Col9 to the mix might create some pain. I could add Col9 as an included column, but I need to be aware that my overlapping index might not be quite what I want. Overlapping indexes aren’t duplicates, even though they might be able to function well enough for each other. If you have time, check the queries where they’re used and whether performance changes if one isn’t there.
Then we have the matter of size – do we really want to increase the size of the database with an extra index? Maybe check to see how big the index would be, and the impact on disk space, backups, all that. Would a filtered index work instead?
But also, why is a new index needed? Is there a new query, or has an old one changed? Perhaps the solution lies in changing the query instead? Or perhaps a “good-enough” improvement will suffice by leveraging a different index with maybe an extra included column added. Maybe the ideal index can allow the query to go straight to the row when joining to that table, but maybe a different index can target the row enough (even if a residual predicate is needed) to reduce the cost of the expensive query.
What it all comes down to is the amount of compromise that I’m okay with. I know there’s a risk to adding a new index. I know there’s a risk to modifying an existing one (even adding an extra included column isn’t cheap), so it comes down to considering the options and weighing the urgency against as many of the other factors that can be seen. There is always risk, which is reduced somewhat by being familiar with the database environment, but understanding what the reward really is (unhappy users, for example?), the cost can be weighed.
The worst times are when something needs to be fixed urgently, and there isn’t the time to learn enough about the system. This scenario means it’s likely to be a new customer, but this is where it helps to be the one who’s been called in to rescue things. When there’s been a new release of some software and performance has tanked, new indexes tend to go in a lot more freely than when the issue is less urgent.
But you should take time when making any change. As we grow in expertise (hopefully not just by making mistakes), it becomes easier to recognise the risks and rewards, so that good decisions can be made even when the problem is urgent.
@robfarley.com@bluesky (previously @rob_farley@twitter)
