Rob Farley thinks about costs and benefits:
“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.
Read on for Rob’s thoughts.