As Jack pointed out this is most likely because somehow you are stuck with a bad execution plan.Is this an adhoc query or is this in a stored procedure? I have seen this happen with stored procedures because of parameter sniffing. But, with plan reuse, it could happen in ad-hoc queries as well. If this is a production server and you are concerned about doing DBCC FREEPROCCACHE, you can try these alternatives. (I am really shooting in the dark thinking that something will stick).1. Change your query to where v.newdate = @begin OPTION (RECOMPILE)
This forces a statement level recompilation every time the query is run. Whether this is good or not depends on how complex the statement is. If it is a simple select with a where clause the recompilation penalty may be small enough.2. Change your query to where v.newdate = DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
This is based on a well-established technique called "shoot-in-the-dark".3. Change your query to this:declare @begin datetimeset @begin = DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)declare @dummyBegin DATETIME;set @dummyBegin = @Begin;...-- and thenwhere v.newdate = @dummyBegin;
This again is based on the technique used in #24. Change your query to this:create table #tmpDate (dummyDate DATETIME);insert into #tmpDate (dummyDate) VALUES (@begin);-- and thenFROM YourOtherTable v cross join #tmpDate tdWHERE v.newDate = td.dummyDate;
Even though I said "shoot-in-the-dark", all of the above are attempts to give the query optimizer a chance to find a better execution plan. 
Why is my parameter causing a different Execution?
Posted - 2012-08-18 : 23:58:01
