x

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?

more ▼

asked Oct 15, 2009 at 09:01 AM in Default

thatismatt gravatar image

thatismatt ♦♦
210 7 8 9

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Oct 15, 2009 at 09:55 AM

Kev Riley gravatar image

Kev Riley ♦♦
52.8k 47 49 76

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 15, 2009 at 09:30 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.5k 19 21 74

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x408
x245
x127
x36

asked: Oct 15, 2009 at 09:01 AM

Seen: 1654 times

Last Updated: Oct 15, 2009 at 09:01 AM