When Does Recompile Stored Procedures

Normally when a user calls a stored procedure, SQL Server does not create a new data access plan to retrieve the information from the database. The queries used by stored procedures are optimized only when they are compiled. As you make changes to the table structure or introduce new indexes which may optimize the data retrieval you should recompile your stored procedures as already compiled stored procedures may lose efficiency. By recompiling stored procedures you can optimize the queries. There are three ways in which you can force SQL Server to recompile your stored procedure:

refrence click:: http://support.microsoft.com/kb/243586

When a user executes a stored procedure, if it is not already available in cache, SQL Server loads the procedure and compiles a query plan. The compiled plan is saved in cache and is reused by subsequent callers of the stored procedure until some action occurs to invalidate the plan and force a recompilation. The following actions may cause recompilation of a stored procedure plan:

•Dropping and recreating the stored procedure

•Restoring the database containing the stored procedure or any object referenced by the stored procedure

•The stored procedures plan dropping from the cache

•Use of a WITH RECOMPILE clause in the CREATE PROCEDURE or EXECUTE statement.

•Schema changes to any of the referenced objects, including adding or dropping constraints, defaults, or rules.

•Running sp_recompile for a table referenced by the procedure.

•Restoring the database containing the procedure or any of the objects the procedure references (if you are performing cross-database operations).

•Sufficient server activity causing the plan to be aged out of cache.

•Rows changed threshold: There are thresholds on the number of row changes that cause stored procedure recompilation, depending on the type of object as follows:

  Table type- Permanent  
  Empty condition -< 500 Rows
  Threshold when empty -# of Changes >= 500 
  Threshold when not empty -# of Changes >= 500 + (20 percent of Cardinality)

  Table type- Temporary   
  Empty condition -< 6 Rows
  Threshold when empty -# of Changes >= 6
  Threshold when not empty -# of Changes >= 500 + (20 percent of Cardinality)

  Table type-Table Variable  
  Empty condition -No thresholds
  Threshold when empty -No thresholds
  Threshold when not empty -No thresholds

The above thresholds, recorded by Profiler under the SP:Recompile event, govern the recompilation of permanent and temporary tables. The data column EventSubClass provides the reason for recompilation. When a threshold is crossed, EventSubClass will = 2, indicating that statistics changed. Row changes are recorded in the rowmodctr column of the sysindexes table.

•Use of certain SET options in stored procedures can cause recompilation.

1.Generally, those that affect query behavior or result sets such as: 
  ANSI_DEFAULTS 

  ANSI_NULL_DFLT_OFF 

  ANSI_NULL_DFLT_ON 

  ANSI_NULLS 

  ANSI_PADDING 

  CONCAT_NULL_YIELDS_NULL 

  FORCEPLAN 

2.Other SET options include: 
  ANSI_WARNINGS 

  ARITHABORT 

  LANGUAGE 

  NUMERIC_ROUNDABORT 

  QUOTED_IDENTIFIER

All these reasons for recompiling a stored procedure did exist in earlier versions, and caused the plan to recompile before beginning execution of the procedure. In SQL Server 7.0, a new behavior is introduced that may cause a stored procedure to recompile during execution. This new behavior ensures that the optimizer always has the best possible plan for each specific statement within a procedure. The following events may cause a run-time recompilation of a stored procedure:

•Stored procedure will recompile if there is a sufficient number of rows in a table referenced by the stored procedure has changed. SQL Server will recompile the stored procedure to be sure that the execution plan has the up-to-date statistics for the table.

•The procedure interleaves Data Definition Language (DDL) and Data Manipulation Language (DML) operations.

•Stored procedures will recompile if the developer has place interleaving Data Definition Language operations with Data Manipulation Language operations. This is usually caused when temporary objects are created and referenced throughout the code.

•The procedure performs certain operations on temporary tables.

In some cases, the cost of recompiling the stored procedure is more than the benefit derived from doing so, especially for large procedures. It is very important to note that when a recompilation is triggered, the entire batch or procedure is recompiled. This means that performance degradation is directly proportional to the size of the procedure or batch.

refrence click :: http://msdn.microsoft.com/en-us/library/ms190439.aspx

(a) The sp_recompile system stored procedure forces a recompile of a stored procedure the next time it is executed. For example:

Exec sp_recompile [MySPName]

(b) Create a stored procedure specifying WITH RECOMPILE option.

If WITH RECOMPILE is specified SQL Server does not cache a plan for this stored procedure;

the stored procedure is recompiled each time it is executed. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure, resulting in different execution plans to be created each time. Ideally, you should not use this option because by using this option, you lose most of the advantages you get by substituting SQL queries with the stored procedures. However, you can use this option during early development stages of the project where database structure is still evolving and stored procedures should be recompiled as a result of these changes.

Here is how you can create a store procedure using RECOMPILE option:

CREATE PROCEDURE usp_MyProcedure WITH RECOMPILE
AS
Select SampleName, SampleDesc From SampleTable

GO

(c) You can force the stored procedure to be recompiled by specifying the WITH RECOMPILE option when you execute the stored procedure. Use this option only if the parameter you are supplying is atypical or if the data has significantly changed since the stored procedure was created.

For Example:

EXEC usp_MyProcedure WITH RECOMPILE

Vedio Link http://www.novicksoftware.com/videorecompile_profiler.htm

Thanks

Yogesh

The sp_recompile system stored procedure forces a recompile of a stored procedure the next time that it is run.

Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled every time that it is executed.

If we 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.

The following will force a stored procedures to recompile:

1. Adding indexes(if optimizer sees it improves)
2. Massive skew of data
3. Changing data in indexed columns
4. dropping and recreating the stored procedure
5. using the WITH RECOMPILE clause in the CREATE PROCEDURE or the EXECUTE statement
6. changing the schema of any referenced objects
7. running the sp_recompile system stored procedure against a table referenced by the stored procedure
8. restoring the database containing the stored procedure or any object referenced by the stored procedure
9. the stored procedures plan dropping from the cache
10. Stored procedure will recompile if there is a sufficient number of rows in a table referenced by the stored procedure has changed. SQL Server will recompile the stored procedure to be sure that the execution plan has the up-to-date statistics for the table.
11. Stored procedures will recompile if the developer has place interleaving Data Definition Language operations with Data Manipulation Language operations. This is usually caused when temporary objects are created and referenced throughout the code.
12. When Parameter sniffing happens
13. Set operation(Mainly on SQL server 2000)

SET ANSI_DEFAULTS OFF 
SET ANSI_NULLS OFF 
SET ANSI_PADDING OFF 
SET ANSI_WARNINGS OFF 
SET ARITHABORT OFF 
SET CONCAT_NULL_YIELDS_NULL OFF 
SET DATEFORMAT dmy 
SET FORCEPLAN ON 
SET LANGUAGE svenska 
SET NUMERIC_ROUNDABORT ON 

Notes: SQL Server 2008 R2 features statement-level recompilation of stored procedures. When SQL Server 2008 R2 recompiles stored procedures, only the statement that caused the recompilation is compiled, instead of the entire procedure. As a result, SQL Server uses the parameter values as they exist in the recompiled statement when regenerating a query plan. These values may differ from those that were originally passed into the procedure.

Eg:

USE pubs 
SET NOCOUNT ON 
GO 
--Create dummy proc so we can do ALTER PROC when we generate the proc 
CREATE PROC p AS 
PRINT 'dummy' 
GO 
--Create table to hold the SET parameters to set: 
CREATE TABLE ct (cn VARCHAR(100) PRIMARY KEY) 
--Populate the table 
INSERT ct VALUES('SET DATEFIRST 2') 
INSERT ct VALUES('SET DATEFORMAT dmy') 
INSERT ct VALUES('SET DEADLOCK_PRIORITY LOW') 
INSERT ct VALUES('SET LOCK_TIMEOUT 200') 
INSERT ct VALUES('SET CONCAT_NULL_YIELDS_NULL OFF') 
INSERT ct VALUES('SET CURSOR_CLOSE_ON_COMMIT ON') 
INSERT ct VALUES('SET DISABLE_DEF_CNST_CHK ON') 
INSERT ct VALUES('SET LANGUAGE svenska') 
INSERT ct VALUES('SET ARITHABORT OFF') 
INSERT ct VALUES('SET ARITHIGNORE ON') 
INSERT ct VALUES('SET ANSI_NULLS OFF') 
INSERT ct VALUES('SET NOCOUNT OFF') 
INSERT ct VALUES('SET NUMERIC_ROUNDABORT ON') 
INSERT ct VALUES('SET QUERY_GOVERNOR_COST_LIMIT 30') 
INSERT ct VALUES('SET ROWCOUNT 30') 
INSERT ct VALUES('SET TEXTSIZE 3000') 
INSERT ct VALUES('SET ANSI_DEFAULTS OFF') 
INSERT ct VALUES('SET ANSI_PADDING OFF') 
INSERT ct VALUES('SET ANSI_WARNINGS OFF') 
INSERT ct VALUES('SET FORCEPLAN ON') 
INSERT ct VALUES('SET IMPLICIT_TRANSACTIONS ON') 
INSERT ct VALUES('SET REMOTE_PROC_TRANSACTIONS ON') 
INSERT ct VALUES('SET TRANSACTION ISOLATION LEVEL READ COMMITTED') 
INSERT ct VALUES('SET XACT_ABORT ON') 


--Create the proc which in turns create a proc with the various SET parameters inside. 
--Then debug this proc, set a breakpoint and execute the proc named "p" for  
--each iteration and see if it triggered a recompile. 
CREATE PROC generate_proc AS 
DECLARE @sql NVARCHAR(2000), @set_option NVARCHAR(100) 
DECLARE c CURSOR FOR SELECT cn FROM ct ORDER BY cn 
OPEN c 
FETCH NEXT FROM c INTO @set_option 
WHILE @@FETCH_STATUS = 0 
BEGIN 
SET @sql = 'ALTER PROC p AS' + CHAR(13) + CHAR(10) 
SET @sql = @sql + @set_option + CHAR(13) + CHAR(10) 
SET @sql = @sql + 'SELECT au_fname, au_lname FROM authors WHERE au_lname = ''White''' + CHAR(13) + CHAR(10) 
PRINT @set_option 
EXEC(@sql) 
FETCH NEXT FROM c INTO @set_option 
END 
CLOSE c 
DEALLOCATE c 

There are two recompile events available in Profiler in SQL 2008

1. SP:Recompile under Stored Procedures
2. SQL:StmtRecompile under T-SQL

The SP:Recompile event in the SQL Profiler indicates when SQL Server recompiled a stored procedure or a trigger. In SQL Server 2000 Service Pack 2 (SP2) or in SQL Server 2005, the SP:Recompile event provides more information about why the recompilation occurs, which is helpful when you are troubleshooting recompile issues.

SQL Server 2005 Reason for recompilation

1. Schema changed
2. Statistics changed
3. Deferred compile
4. Set option changed
5. Temp table changed
6. Remote rowset changed
7. For Browse permissions changed
8. Query notification environment changed
9. Partition view changed
10. Cursor options changed
11. Option (recompile) requested
12. Dropping and recreating the stored procedure
13. Using the WITH RECOMPILE clause in the CREATE PROCEDURE or the EXECUTE statement
14. Restoring the database containing the stored procedure or any object referenced by the stored procedure
15. The stored procedures plan dropping from the cache

Schema Changes

This recompile occurs when any of the base tables for the query have changed, when any of the indexes for the query have been rebuild or changed and when sp_recompile is run for any of the base tables.

It does not occur for a procedure if that procedure is altered, nor does it appear if sp_recompile is run for the procedure. Both of those result in a sp:CacheRemove event.

Statistics Changed

The recompile occurred because statistics that the statement in question used have changed.

The SET statement in a stored procedure causes a recompile:

Set arithabort
Set ansi_null_dflt_on
Set ansi_defaults
Set ansi_warnings
Set ansi_padding
Set concat_null_yields_null

Deferred compile
A deferred compile occurs when an object referenced by a statement within the batch does not exist when the batch is first compiled.

CREATE PROCEDURE [dbo].[TestingCacheEvents2]
AS

SELECT ID, SomeDate, Status
FROM TestingCacheEvents
WHERE Status = 'M'

CREATE TABLE #Temp (Status char(2), CountStatus int)

INSERT INTO #Temp
SELECT status, count(*)
FROM TestingCacheEvents
GROUP BY Status

DROP TABLE #Temp

Option Recompile requested

CREATE PROCEDURE [dbo].[TestingCacheEvents5]
AS

SELECT ID, SomeDate, Status
FROM TestingCacheEvents
WHERE Status = 'M'

SELECT Status, COUNT(*)
FROM TestingCacheEvents
GROUP BY Status
OPTION (RECOMPILE)

http://sqlinthewild.co.za/index.php/2010/11/18/recompiles
http://support.microsoft.com/kb/308737

Why to Recompile stored procedure?

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. This optimization occurs automatically the first time a stored procedure is run after SQL Server is restarted. It also occurs if an underlying table that is used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not occur until the next time that the stored procedure is run after SQL Server is restarted. In this situation, it can be useful to force the stored procedure to recompile the next time that it executes

Another reason to force a stored procedure to recompile is to counteract, when necessary, the "parameter sniffing" behavior of stored procedure compilation. When SQL Server executes stored procedures, any parameter values that are used by the procedure when it compiles are included as part of generating the query plan. If these values represent the typical ones with which the procedure is called subsequently, then the stored procedure benefits from the query plan every time that it compiles and executes. If not, performance may suffer.

Forced Recompilation

SQL Server provides three ways to force a stored procedure to recompile:

1. The sp_recompile system stored procedure forces a recompile of a stored procedure the next time that it is run. It does this by deleting the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure is run.

2. Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled every time that it is executed. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure and cause different execution plans to be created every time. Use of 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.

   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 behavior noted earlier in this section, but 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 atypical or temporary values are used in only a subset of queries that belong to the stored procedure.

3. You can force the stored procedure to be recompiled by specifying the WITH RECOMPILE option when you execute the stored procedure. Use this option only if the parameter you are supplying is atypical or if the data has significantly changed since the stored procedure was created.