Stored procedures can provide performance benefits due to the cached query execution plan,
allowing SQL Server to reuse an existing plan instead of generating a new one.
However, Stored Procedures are not the solution for best database performance.
We still need to account for the performance of individual statements within the body of your stored procedure and to make sure that the tables are indexed properly and that the database is designed efficiently.
A recompilation occurs when a stored procedure’s plan is re-created.
Re-compilations occur automatically during stored procedure execution when underlying table
or other object changes occur to objects that are referenced within a stored procedure. Automatic recompilations can also occur with changes to indexes used by the plan or after a large number of updates to table keys referenced by the stored procedure.
This is done to make sure the SQL Server execution plan is using the most current information and not using out-of-date assumptions about the schema and data.
Although recompilations are costly and should be avoided most of the time, there may sometimes
be reasons why you would want to force a recompilation.
For example, if we have stored procedure that produce widely different results depending of the parameters sent on the SP execution. For example when SP is executed with one parameter, the procedure returns one row and executed with another parameter returns millions of rows.
In that case SQL server may end up with caching plan that is optimized only for single row not for millions of rows, causing poor performance.
Recompilation is achieved with placing the WITH RECOMPILE statement in the stored procedure definition after defining the parameters.
SQL Server also uses statement-level recompiles within the stored procedure, instead of
recompiling the entire stored procedure. Since recompiles cause extra overhead in generating new
plans, statement-level recompiles help decrease this overhead by correcting only what needs to be
We should use recompilation when we want to use other benefits of stored procedures like security and modularization, but we don’t want SQL Server to store inefficient execution plan and with that to cause bad performance.