>>>>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.

Recompiling indeed has a cost, but for the majority of queries it is minor. I'd certainly not advise to apply recompilation for each and every query (as with a heavy server load it might indeed add considerable CPU loads on your SQL server), but only for those that have been identified to cause performance problems due to inefficient execution plans when parameter values change.

In SQL Server 2008 and up there is a OPTIMIZE FOR UNKNOWN clause that will optimize the query to performance as much as possible without knowing the values passed. That might be an option for you. Do not expect it to choose the best plan, but it will not go ballistic when your parameter values required an total different execution plan because of changed parameter selectivity.

In regards, of your remark that the optimizer is a non-intelligent computer program: My experience is that the optimizer is far more capable to determine the best execution plan than all, but a very few experts. Therefore you should avoid hinting the optimizer unless you really have no other choice. Hinting the optimizer might drag you down into all kinds pitfalls of performance problems. A hint could be beneficial for one particular value, at one specific point in time, but could bite you severely when e.g., row numbers, index statistics, table structures, hardware etc change.

When you start hinting the optimizer, you're convicted to evaluate them regularly whether they are still beneficial. If you really feel you need to be outside the bell curve and have the resources available to keep monitoring the database on performance and make adjustments when necessary, just go ahead. But please be aware this is a specialism that is not for the fainted hearts and you really should know what you are doing.

The one thing you should do, IMO, is make sure that the optimizer can use the right indexes, by making sure those are available. solving performance by tweaking you index IMO is the best strategy. Its not easy, I'll be the first to admit, but will save you a lot of trouble in the end.

I've had my share of bad experiences with hinting the optimizer. In this case I was hinting the optimizer to use a LOOP JOIN iso of a MERGE JOIN. Worked perfectly for the cases I had identified, but over time, as the database grew went bad. By making sure the right index (which is an art int itself) was available, the optimizer was able to pick a plan that was much better than what I had in mind.



Walter,