R. C. van Dijk (8/26/2009)
I totally agree. The complex queries I was talking about perform a lot of different actions, complex business logic is implemented in SP's. These kind of statements (seperate steps/parts of the SP) have inputs of which the domains are well defined. The individual parts of the resulting execution plan are stable. That is, until the described scenario happens. This will through everything of and a rethink of the rules, design etc is in place.The fact that the inputs can mess up the execution plan, means that the domains of the input are not well defined.
If defining the domains is impossible, you are writing a system in which (parts of) the data is can not be defined upfront and you are probably better of rethinking the use of a relational database system (and a raise, because writing this is hell).
In a stable situation, the resulting plan should always be predictable and stable. If this was not the case, optimising would be useless.
This is actually incorrect. If you are dealing with very large databases, with many, many users, there can be a large variance in the size of the pie that users are interested in, and I have seen situations where, for example, a clustered index scan performed much better for a query returning a very large number of rows while a nonclustered index lookup performed better for a user concerned with a much smaller set of data. It has nothing to do with the definition of domains, which are well understood. It seemed to me by your own admission that the exec plans were not stable, because they became suboptimal as the amount of data increased. This is not really that different from what I was talking about, except that both conditions exist simultaneously: the exec plan to deal with a small amount of data is not the same as the one to deal with a large amount of data from the same table. Let's define inputs as parameters supplied to sp_executesql, for example, because that's a clear situation where inputs can change without causing a new execution plan to be generated. So, optimizing can be useless in the conventional sense, because there is no optimal. Well, there is an optimal, but it cannot be perfect.