>>>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,
>>
>>You can use the OPTIMIZE FOR query hint to tell SQL which cached plan to use rather than RECOMPILE.
>>http://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/
>
>I think you are misunderstanding what it does. It still will only use ONE execution plan for a query. You are only hinting the optimizer to use a particular value for the construction of the execution plan and will use that for whatever value is passed. If you want to use a different execution plan, you have no choice but to change the query. Note that the optimize for clause is part of the query. If you change the value used in the OPTIMIZE FOR clause, ** IT IS A DIFFERENT QUERY ** with its own execution plan.
>
>I would never propose using this hint for the very same reasons outlined in the article, unless you want to balance the execution plan to avoid terrible performance for either 'elephant' or 'mouse'. In this case you'd never expect optimum performance for each possible value.
>
>>I understand the time the query takes is probably the longest part of the process. That does not mean one should dismiss other potential speed gains. I'll take every enhancement I can get as well as getting the queries to run faster.
>
>The problem with your proposal is that you might gain a few microseconds on eliminating the construction of a new executing plan, but lose a 1000x or 1.000.000 times more on a non-optimal execution plan. In general the SQL optimizer is (providing the right indexes are there) a lot more capable of choosing the right execution plan than the developer. The OPTIMIZE FOR clause, IMO is just a patch for very specific problems that in general are better resolved by just recompiling the execution plan.
>
>Only in very specific cases where it takes considerable time to create a complex execution plan and performance is critical, I'd decide otherwise. In all other cases where performance is just acceptable, I do not even bother. If a query takes a second to execute, I'm not investing any time to gain one or two miliseconds in messing around with caching, non caching or hinting execution plans. The gains must be at least 100% for anything taking less than a second anything less is a waste of my time.
>
>Walter,

You're in the majority there. I feel most developers are in the majority of the bell curve. I'm looking for those on the leading side of the curve. The optimizer is a non-intelligent computer program. The article says there are performance problems with recompiling - especially when scaled to thousands of users. I can't prove the optimize hint tells it to rebuild the plan or switch between different plans at the moment.