#WITH RECOMPILE SQL FREE#

OPTION (RECOMPILE) was added in SQL 2005. Keep in mind that WITH RECOMPILE has been in the product since at least 4.x.

I really hate needing to add any forced recompiles on SPs that run in 0.1 seconds when they work right, but what choices do we have? But it COULD - a first compileĬould mark which statements are most affected by statistics and/or parameters, and JUST THOSE could automagically recompile as needed, 'WITH SMART RECOMPILE'. but I guess that isn't clear until too late. I think what it is, is that I'd *like* 'with recompile' to act like it has 'option(recompile)' on just the statements that can really benefit from it. Kendra's article does not discuss these aspects. So, you say, let's see, it's going to cost me 0.4 seconds of recompile every call, to keep it from blowing up to 10.0 seconds every second Wednesday. And, well, you may have a lot of slack to go with, when the SP normally runs in 0.2 seconds with 6k reads, until it goes nutsĪnd runs in 10.0 seconds with 9m reads (I begrudge the reads almost more than the time). So, the recompiles, either of these, is going to cost you something on every execution. So you go back to the SP and wonder, how do I keep it from blowing up at random times like that? Well, with option statements, Then it gets some bad parameters in its teeth and the plan blows up. Tom, I don't see that that link quite answers the question.Īnd, I didn't really mention the real problem, although it's as common as dirt and it's the reason I'm asking.įirst, you right an SP and it seems fine. (And the behaviour with variables as constants appeared first in SQL 2008.)

So is there any situation you would use WITH RECOMPILE rather than OPTION (RECOMPILE)? From a pure performance point of view I cannot think of a case, but I know that I have used it in a few places where I have shared temp tables between stored procedures,Īnd I have gotten weird errors due to the cached plan for the inner procedure. Values, and this will be a scan of some sort. For this reason, the optimizer must build a plan which is correct for all If you use OPTION (RECOMPILE), the variables will be handles as constants, and the correct index will be used.īut with WITH RECOMPILE, SQL Server does not know the run-time values of to even of they are parameters to the procedure (since they can change inside the procedure).

#WITH RECOMPILE SQL FREE#

The other is that OPTION (RECOMPILE) can give you better query plans. You specify using sprecompile to recompile the procedure on its next execution You free the procedure cache You change the procedure definition SQL Server itself decides to recompile because it thinks it to be advantageous You Restart the SQL Instance, same as freeing procedure cache. One is that OPTION (RECOMPILE) only affects the statement in question, while WITH RECOMPILE requests recompilation of the lot. If not, performance may suffer.Is 'option(recompile)' more efficient than 'with recompile'? If these values represent the typical ones with which the procedure is called subsequently, then the stored procedure benefits from the query plan each time it compiles and executes. When SQL Server executes stored procedures, any parameter values used by the procedure when it compiles are included as part of generating the query plan. In this situation, it can be useful to force the stored procedure to recompile the next time it executesĪnother reason to force a stored procedure to recompile is to counteract, when necessary, the 'parameter sniffing' behavior of stored procedure compilation. This does not execute the procedure but it does mark the procedure to be recompiled so that its query plan is updated the next time that the procedure is executed. But if a new index is added from which the stored procedure might benefit, optimization does not happen until the next time the stored procedure is run after Microsoft SQL Server is restarted. To recompile a stored procedure by using sprecompile Select New Query, then copy and paste the following example into the query window and click Execute. It also occurs if an underlying table used by the stored procedure changes. This optimization happens automatically the first time a stored procedure is run after Microsoft SQL Server is restarted.

'As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them.

With recompile sql