Introduction

Before a query, batch, stored procedure, trigger or dynamic SQL statement begins execution in SQL Server, it is compiled into a plan which is called "Query Plan". This compiled plan is stored in a part of SQL Server's memory named the "plan cache".

Before SQL Server begins executing of a query plan, the server checked for the validity (correctness) and optimality of the plan. If one of the checks fails, the plan cache would be deleted and the statement corresponding to the query plan or the entire query would be compiled again. Then a new plan cache and possibly a different query plan produced. This process called re-compilation.

What is the problem?

To clarify more about the effects of compilation and recompilation, note the following scenarios:

Scenario 1: In a search page users can look up by any of several columns after passing a parameter indicating which column must be searched. The parameter is examined using a CASE block, and then executed, depending on the specified column. The performance (execution time) of each search must be approximately the same, but it isn't. Performing multiple searches, you find that the first is fast, and the subsequent searches are much slower.

Scenario 2: You plan to design a complex multi-statement stored procedure. On testing, you discover that the stored procedure occasionally takes a longer than expected time to execute.

In both scenarios SQL Server creates a query plan at the first procedure call, and stores it in memory. As long as you follow the cached plan (searching on the same column), SQL Server finds no changes and uses the cached plan. The next time you decide to search on another column, SQL Server blindly uses the previous plan which is not valid for the current parameters. This time, execution would be divided into two parts. First, SQL Server follows the previous cached plan which is certainly saves the execution time and affects the performance. Second, it creates new query plan and stores it in memory for next run. Consequently, you noticeably realize a longer time to complete the request.

Forcing a stored procedure to recompile

Although re-compilation is helpful in preventing from performance plummeting, unwise recompilation can cause more time consumption. Therefore, this is your right to recompile a stored procedure for each call in specific condition. In the following section, we will discuss the various available methods forcing SQL Server to recompile the stored procedure for next run(s).

sp_recompile

The sp-recompile system stored procedure forces a recompile of a stored procedure for the next run. It occurs by deleting the existing query plan from the cache, forcing a new plan to be released for the next procedure run. In the following code, sp_recompile will cause recompilation of the Sales.Customer procedure for the next time it is run.

USE AdventureWorks2008R2;
GO
EXEC sp_recompile Sales.Customer';
GO

WITH RECOMPILE

The WITH RECOMPILE option indicates that SQL Server does not cache a plan for the stored procedure. This option forces the stored procedure to be recompiled on every execution time.

It is important to know that using this option is uncommon and causes the stored procedure to execute more slowly, because the stored procedure must be recompiled every time that it is executed. Thus, be aware of the adverse effects of this option on query execution time and use this option only in proper circumstances.

You can use the WITH Recompile option in Exec command of the procedure, or directly before the AS keyword in procedure definition. See the following examples:

EXEC dbo.ProductByDate(2012) WITH RECOMPILE

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE dbo.uspProductlist @Name varchar(30)
 WITH RECOMPILE
AS
    ... 

Recompile Query Hint

If you only want individual queries inside the stored procedure to be recompiled, instead of the complete stored procedure, specify the RECOMPILE query hint inside each query you want recompiled.

This behavior mimics SQL Server's statement-level recompilation, which means only the statement that caused the recompilation is being compiled rather than the entire procedure. In addition to using the stored procedure's current parameter values, the RECOMPILE query hint also uses the values of any local variables inside the stored procedure when compiling the statement. Use this option when temporary values are used in only a subset of queries that belong to the stored procedure.

In summary, query plan which is created after the first run of stored procedure, is being used in next procedure call. Running stored procedure directly from this cached plan, improve the performance by reducing the execution time. In the case of creating new query plan for a stored procedure, recompilation occurs which can have pleasant or unpleasant effects on the query performance. Therefore, it must be carefully managed by developer to use in appropriate conditions. This article helps you to decide when and how you should recompile a stored procedure to boost the performance.

References:

http://technet.microsoft.com/en-us/library/cc966425.aspx

http://msdn.microsoft.com/en-us/library/ms190439(v=sql.100).aspx