siddharth ranga

• Sep 29th, 2005

 

Recompile is useful when the tables referenced by the stored proc undergoes a lot of modification/deletion/addition of data. Due to the heavy modification activity the execute plan becomes outdated and hence the stored proc performance goes down. If we create the stored proc with recompile option, the sql server wont cache a plan for this stored proc and it will be recompiled every time it is run.

Rose Mary

• Dec 24th, 2016

 

When a stored procedure is executed for the first time, SQL Server optimizes and compiles it. A query plan is created and stored in cache for the stored procedure. On subsequent executions, SQL Server looks in the cache for the stored procedure, if the stored procedure is found it is executed without compilation. It only compiles it if the stored procedure is not found in the cache. This slows down the execution of the stored procedure if it is executed multiple times, each time with a separate parameter passed to it, since it follows the same old query plan. WITH RECOMPILE option allows creation of a new query plan for the stored procedure, every time it is called. This speeds up the execution of the stored procedure significantly.