change optimizer’s plan on using indexes

When I run a query as follows, the run time is less than 1 sec.

SELECT @tenor_from =CONVERT(DATETIME,MIN(spc.maturity_date),103)  
    FROM source_price_curve spc   
    WHERE spc.as_of_date >= @as_of_date_from

It utilizes the non clustered index based on maturity date column in the table.
I then added a temp table in join as follows

SELECT @tenor_from =CONVERT(DATETIME,MIN(spc.maturity_date),103)  
FROM source_price_curve spc  
INNER JOIN #source_price_curve_list spcl
    ON spc.source_curve_def_id  = spcl.price_curve_id  
WHERE spc.as_of_date >= @as_of_date_from

In this case also same index is seen utilized(unless recompiled or forced to use new index by hint),however, performance was decreased dramatically(to 12 secs),which was solved by using cross apply condition(as suggested by Paul).

My concern is, is there any ways to change optimizer’s plan or let the optimizer automatically choose a new index as used in join or filter condition by new query ?
(As far as I know, the optimizer uses the same index unless recompiled or forced to use a new index as a hint.)

Thanks in advance.

Your two queries are different, and have different execution plans. While they may both use a particular index, that index was chosen independently. The fundamental tasks the queries are doing are different (one has one table, one uses two), and thus they cannot possibly use the same plan.

Once the optimizer has identified a plan for a given query, it will reuse that plan as you indicate. However, the queries have to be the same. Even changing a hard-coded value will often cause a new plan to be generated (see Parameters and Execution Plan Reuse in the product documentation).

As a general rule, plans that are good sometimes but bad others are normally due to queries with variables in them, or queries in procedures and functions that use passed-in parameters in queries.

The best way to help the optimizer make the best choices is to ensure that your statistics are up-to-date. When the optimizer makes good estimates as to how many rows will be found, its plans are more likely to be good.

There are relatively rare cases where the optimizer will refuse to pick the best possible query; in those rare instances, you can use various query hints to (for example) force the use of a particular index.

That said, hints should generally be used very sparingly, especially in recurring code (scripts run regularly, applications, etc.). Using a query hint like this will prevent the optimizer from considering certain options, and the odds are good that what’s the best option today may not be the best in a month or a year, as your data changes. Your “perfectly planned” query today may become a crippling bottleneck next month, next week, or even tomorrow. Instead of query hints, you can also use plan guides (I know no more about those than that they exist).

Most often, the best solution is to tune the query so a better index will be chosen, or so less work needs to be done. This may sometimes feel like finding a way to “trick” the optimizer into doing what you want. However, it’s really a matter of explaining to the optimizer what you want to do better. If you’re speaking a foreign language, and you’re asking to get to a bathroom and use the toilet, but what you actually say is “I need to use your bathtub,” whose fault is it if the person you’re talking to goes to get you a towel instead of taking you straight to the nearest toilet?