This is on SQL 2005 SP2, but I suspect this is something that applies to all query hints in general.
I've got an adhoc sql statement that gets a different query plan solely because of OPTION(recompile). The batch statement is never (in practice) being reused as it is using non-parameterized dynamic sql generation where the statement changes with dates / other parameters. I've confirmed this is the case by checking the cached plan_handle, always a different ID as values in batch change.
When I do dbcc freeproccache I expect that the query plan I get for the statement would be exactly the same as one with "option (recompile)" query hint added. But its not, its quite different actually (much much faster).
Does anyone know why addition of a query hint would cause the engine to pick a different query plan?
The statement is something like this
dbcc freeproccache
go
sp_executesql '
declare @begindate datetime
declare @enddate datetime
select @begindate='1/1/2011'
select @enddate='2/1/2011'
select count(*) from tableA where tableA.datecolumn between @begindate and @enddate
and exists(
select A
union all select B
union all select C
)'
all I have to do is change the statement to use recompile query hint and I get different (much better) query plan.
dbcc freeproccache
go
sp_executesql '
declare @begindate datetime
declare @enddate datetime
select @begindate='1/1/2011'
select @enddate='2/1/2011'
select count(*) from tableA where tableA.datecolumn between @begindate and @enddate
and exists( select A
union all select B
union all select C
) OPTION (RECOMPILE)'
As code is passing in the date values I can't use a plan guide to override the default behavior, looks like code change will be required. But I wish I knew where to look on why recompile would force a different plan to be used.