WITH RECOMPILE has been a new friend of mine for few months only now,

we have a few SSRS reports running against our data warehouse and some of them have many parameters, including a date range

and basically without WITH RECOMPILE SQL Server will simply cache the plan used for the first execution after the CREATE statement runs, then use it for all subsequent executions... (which so far is exactly what we would want most of the time)...

but the plan the optimizer will use and cache for a request that had a very wide date range and say 'null' in all other input params will not be optimal for let's say the next request which may have a much narrower date range and a few other input parameters set to different values

WITH RECOMPILE shouldn't be used blindly but in some cases (vast minority...) it turns out that the overhead of recalculating the plan each time is less then the overhead of having a less than optimal plan running.

On an OLTP system in the past I personally never found WITH RECOMPILE useful.

Claude