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.
2018-04-23