philcart (7/30/2010)
@wbrianwhite & @NadrekThe cost of adding and updating the indexes is actually a lot smaller than it's made out to be. Only under extreme change (meaning insert/update/delete) activity will you notice performance degradation from index updates. I think it would be safe to say that a vast majority of SQL Server databases don't fit into the "extreme" category.
For most businesses, the cost of adding the index is much less than the cost of re-developing a query/procedure. Especially when it's vendor supplied code and they have to pay for code changes 😉
Yes, in some cases a re-write is warranted, but why not give the end users some benefit straight away and put any re-writes on the work queue?
The operational cost includes disk space to house the index, disk space and time to back it up, time to correctly figure out the right fillfactor, and maintenance window time to defragment the index regularly (however often or rare that happens to be - the default fillfactor of 100 tends to be pretty bad about requiring maintenance).
When writing new SQL, why not take a little more time and do it well, instead of randomly? For existing bad SQL, most often I see that a few minutes rewriting is extremely beneficial, and has no bad side effects.
In one nontrivial case, 45 minutes of rewriting resulted in four orders of magnitude of performance improvement. In many cases, 5 to 10 minutes of rewriting results in anywhere between double and an order of magnitude more performance.
Essentially: Why add cost in one place to reduce cost in another before you attempt to reduce cost without adding any?