SNIP

>>I'm not quite sure what you mean running the same query with a different number of parameters. Its not relevant in the discussion as the query is setup to accept two parameters. You cannot run the same query with only one, else it is not the same query and would therefore have two different execution plans.
>
>Of course it is attempted. Often queries are written with optional parameters nullified.

Whether the parameter is nullified or not does not really matter: it still is the same query. Remember SQL server looks at the plain text of the Query (thus excluding the values of the parameters), taking a CRC of the plain text and uses that as a key for storing the execution plan. Whether you
use one parameter or not, the text of the query does not change and will therefore use the same execution plan.

In a sense what you are describing might be one of the cases where you want to use RECOMPILE as with optional parameters the ideal execution plan changes when you make a parameter value optional or not. See query below: it does not matter whether you nullify a parameter or not... the query stays the same.

>>Caching execution plans is often beneficial, but not always so. Certain types of plans making use of high selectivity of a particular index value might be (because of the skewness of the distribution of values in the index) low selective for a different parameter value. This might cause the query to run extremely fast for one set of parameters values, but exceptionally slow for another. In the latter case, the other values need a different execution plan than the first set of values. In this case a RECOMPILE is beneficial.

>I'm suggesting that having ad-hoc sql alternate between two existing plans for optional parameters, should be faster than recompile all for every run

Then you have to write two different queries one for 1 parameter and one for 2. You cannot have two different execution plans for one Query.
But again, RECOMPILE is not only necessary for optional parameters, but can also be beneficial if different parameter values induce different levels of index selectivity and therefore is not executed optimally with the execution plan of the previous value.

Consider:

SELECT * FROM Patient WHERE pat_state = @P1 AND Pat_dob BETWEEN @P2 AND @P3

@P1 = "OH", @P2 = '19700101', @P3 = '19700101'

Now if 95% of you patients live in OHIO, then the optimal plan is to do an index (range) scan on pat_dob and do not bother about the index on pat_state those little few are filtered out after the record has been retrieved from disk

but if the parameters are

@P1 = 'AK', @P2 = '19700101', @P3 = '19700101'

And you only have 3 patients living in Alaska, then the optimal plan will be to do an index scan on pat_state for the value 'AK' and do not bother about using the index on pat_dob. Those will be filtered out after the records have been retrieved from disk

If the queries were executed in the same order as above. the second query will take a lot longer than necessary, as it will do a index scan on pat_dob and will not use the index on pat_state. You can only resolve this by adding RECOMPILE to the execution of the second query.

In this case, despite the extra steps of purging and recalculating the execution plan, RECOMPILE will enhance performance.

>>As for the number of steps you outline above: Its not about the number of steps, but about how much time the overall process costs.
>
>More steps usually translate to longer times. We don't know what is involved in dumping an existing plan during a recompile - since I'm pretty sure a new plan is cached thereafter - versus the time to reuse the existing plan.

I would say that is generally incorrect. There is no correlation between the steps you have listed and the performance of running through the whole list. The execution of the query will make all the other steps insignificant timewise. So generally, you'd be better of in making sure it chooses the right execution plan.

Walter,