We use temp tables in out stored proc and have been experiencing performance issues in retrieving data joining these temp tables with the permanent tables.

Few issues observed is
1. Optimizer does not select all the required index keys of the index
2. Optimizer does not show consitent query plan for the same parameter call.Once it generates a query plan selecting some set of indexes on tables and at other exec of the same call,other set of indexes

I am posting first issue here
There is a permanent table A with columns A1,A2,A3 having a nonclustered index I1 with index keys as A1,A2,A3 in addition to the clustered primary key

When a select operation is performed on this table joining temp table as below,the optimizer selects index I1 with only index key A1 and not A2.This generates high I/O's

select a.A1
from A a,#t t
where a.A1 = t.t1
and a.A2 like t.t2+'%'
and a.A3 between t.t3 and t.t4

but if we do not join the temp table,the optimizer does select all index keys of I1 i.e A1,A2,A3 for the below query reducing I/O's considerably

select a.A1
from A a
where a.A1 = t1_value
and a.A2 like 't2_value%'
and a.A3 between t3_value and t4_value

Initially we thought the "like" operator is causing the issue.But the second query with like operator performs good.

Can anyone please explain this behaviour and suggest solution if any.

Can anyone please suggest some solution/alternative if you have faced any similar situation.