question

thatismatt avatar image
thatismatt asked

Indexes and Stored Procedures

Is is possible to add an index to improve the performance of a stored procedure and accidently cause performance problems for another stored procedure?

stored-proceduresperformanceindexingoptimization
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

Yes. Absolutely. Adding an index could change the way the optimizer chooses to create an execution plan for another procedure in ways that hurt performance.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.