I suspect you have stale stats. The version with the getdate, the optimiser can sniff the values and use those values to get an estimate from the row distribution in the statistics, whereas with variables if can't see the values and hence can only get a generic row estimation. If your stats are off (very common on a table with an ascending datetime column), the version with the function could get a completely incorrect (way low) estimate and generate an exec plan that's optimal for very few rows. When the query runs and gets lots of rows the plan is very, very, very bad indeed.
I've seen a query go from 30 minutes to not finishing after 4 hours because of that problem. The exec plan, if you look only at the estimated plan, looks fine, seeks, key/rid lookups, nested loop joins and low row estimations (like the one row estimated in the plan you attached). It's only with the actual plan you can see the extreme mis-estimate on the row count.http://sqlinthewild.co.za/index.php/2011/03/22/statistics-row-estimations-and-the-ascending-date-column/
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass