RECOMPLE behavior the difference between OPTION ( RECOMPILE ) AND WITH RECOMPILE

An interesting problem was posted on #SQLHelp the other day about using the QueryHint OPTION(RECOMPILE). If you are not sure about how recompile works here is a quick primer. Stored procedures can be expensive to run so SQL Server database engine takes a moment when running the procedure the first time to come up with a plan. Ideally the plan takes into account the current situations and arrives at what it thinks would get the job done in the quickest way with the least resources consumed. This plan is saved in the plan cache. Because a stored procedure is something that gets executed frequently it makes sense to reuse the plan over and over again. But like all things in life situations change and the plan that was working before might not be the best anymore. A simple example would be when the number of rows in the table have changed significantly.

In such cases it is better to RECOMPILE the procedure so that it always takes into account the current state and comes up with a new plan each time. To allow us to do this we have a few option.

Here is the question that was being asked.

WITH RECOMPILE

This hint allows us to recompile the entire stored procedure (not just bits and pieces of it) each time it is executed. The below screenshot show the usage. When used this way we see that the Execution plan is discarded as soon as the query finishes execution. Since we need to recompile it every time the procedure executed there is no sense wasting memory on it.

Result of querying the plan cache can be seen below:-

If the hint was removed in the procedure we would end up with something like below:-

Another way to achieve RECOMPILES without actually modifying the procedure would be to call the hint at run time as shown below

Notice in the above screenshot we don’t have cacheobjtype = CompiledPLan Proc

Another way to recompile procedures is something called statement level RECOMPILE

OPTION (RECOMPILE)

Sometimes we arrive at a use case where the entire stored procedure seems to be fine except for a single block of code. If the procedure is sufficiently complex it doesn’t make sense to RECOMPILE the whole thing. In these cases we can go for statement level recompile. This is achieved by adding the hint OPTION( RECOMPILE) against the statement we want recompiled as shown below.

There is an important distinction that happens at this time. Since we are not RECOMPILING the entire procedures from scratch we end up with a plan in the plan cache. SQL Server still Recompiles the block that has the hint but the rest remains untouched.

Notice that we have use count of 10 and our cache objtype is Compiled plan type Proc.

In summary to answer the question OPTION(RECOMPILE) will result in plan being cached because it does statement level recompile while WITH RECOMPILE will not cache the plan since the entire procedure will get recompiled.