Is is possible to add an index to improve the performance of a stored procedure and accidently cause performance problems for another stored procedure?
Answer by Kev Riley ·
Yep! Couldn't agree with Grant more! That's the beauty of sql performance tuning, and the reason that you should often review your index strategy. Even adding an index to help a stored proc, could cause performance issues to the same proc if enough of the underlying data changes.
Also any index add to help 'read' data will inevitably add more work, and therefore less performance, to any query that 'writes' the same data. Theres always the trade-off between usefulness and maintainability.