SQL: Does having more indexes always make data modifications slower?

As I’ve pointed out in other posts, Betteridge’s Law of Headlines says you already know that the answer to this is no.

There is a persistent myth among SQL Server users that having more indexes always slows things down, particularly anything that needs to modify data (INSERT, UPDATE, DELETE, MERGE).

However, there are two aspects of this to consider:

The first is that you need to decide how much the modifications actually matter in the overall performance of the system. In an earlier blog post, I noted that when I trace typical transactional systems, I see reads making up about 99% of page I/O’s. Writes barely even appear. But I’m not saying they are irrelevant, just that you have to focus on what the system is spending most of its time doing.

Having faster reads (and indexes help a lot here), can make your writes much faster anyway, if that’s the main thing the system is doing.

I understand that some times the write performance is critical, but don’t be confused about write times that are slow because they’re being blocked by read operations either.

The second aspect is that to update or delete something, you have to find it first. (Or for INSERT operations, work out where it needs to go). Whenever I see people showing examples of indexes slowing down updates, they show updates where the primary key of the table is provided. So yes, if all you are doing is updating a row by its primary key, other indexes are just baggage and overhead for that operation.

Updates by primary keys though, are not the only ways that tables get updated. Note the query in the main image above. We’re correcting city names. Anything that was spelled “Smytheville” now needs to be “Smithville”.

You can see the missing index hint here:

No surprise that SQL Server is complaining that to perform this update efficiently, that you need another index. The problem is that to update the city names, we need to be able to find the wrong ones first.

Worse, without that index, imagine which rows SQL Server is going to lock while performing that operation.

This is just a simple example, but I just wanted to make the point that more indexes does not always equal slower updates. It can be quite the opposite.

2018-04-23