Reasons that a stored procedure will recompile
SQL Server 2000 recompiles a stored procedure for many reasons. The first group of reasons that cause recompiles are:
- requests to recompile an individual procedure
- Requests to recompile procedures that depend on a database object, such as a table or view
- Actions that result in the recompilation of all procedures.
In explicit SQL Server terms these reasons are:
- Use of the WITH RECOMPILE option on the CREATE PROC or
ALTER PROC statement. The May 6th issue of my T-SQL UDF of the
Week Newsletter has an article about how to detect which procedures were
created with the WITH RECOMPILE option and has a user-defined function
that lists all such procedures. http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-25-udf_Proc_WithRecompile.htm
- Executing the system stored procedure sp_recompile on the
procedure or on a table or view that the procedure depends on.
- Use of the WITH RECOMPILE option on the EXEC statement
- Use of DBCC FREEPROCCACHE
These reasons are fairly easy to fix. Next month's article has a section on how to locate these easily, and gives some methods for minimizing them when they are really necessary.
The next group of reasons that recompilation occurs is because a change has occurred in a database or database object that invalidates the cached execution plan. These reasons are:
- Restoring the database containing the procedure or any of
the objects that the procedure references. Most often, this involves
restoring a database that is referenced by the stored procedure.
- A schema change in any of the objects that are referenced
by the procedure. This includes the addition or alteration of a column, constraint
or index on a table or view referenced by the procedure.
- Statistics Change. These can be automatically generated by SQL Server or requested with the CREATE STATISTICS or UPDATE STATISTICS statements.
Restoring a database usually will not wait. You will just have to live with any recompiles caused by restores. Schema changes should be scheduled for off hours when any recompiles caused have minimal impact. Statistics changes can happen automatically if the database option 'auto create statistics' is on. Automatic update of statistics was a major issue in version 7 because, when working on temporary tables, SQL Server would recalculate statistics, and therefore recreate the plan, after only five modifications to a temporary table. SQL Server has an improved algorithm for automatic updating of statistics. If this is the reason for recompilations, you may want to consider turning off the automatic generation of statistics.
Memory constraints cause the next reason:
- The plan being aged out of cache.
If this is occurring frequently, you should address the overall memory requirements of the SQL Server instance.
The final two reasons occur in the executing stored procedure. They are the causes that are most amenable to better programming:
- Interleaving Data Definition Language (DDL) and Data
Manipulation (DML) operations. Execution of DDL statements, such as the
creation of permanent or temporary tables and indexes on tables, forces a
recompile at the next DML statement, such an INSERT, UPDATE, or SELECT.
- Using one of these SET statements to change a connection option:
- SET ANSI_DEFAULTS
- SET ANSI_NULLS
- SET ANSI_PADDING
- SET ANSI_WARNINGS
- SET CONCAT_NULL_YIELDS_NULL
Because these two reasons cause most of the recompiles, I consider the problem pretty fixable. It is a matter of modifying the logic of your stored procedure to be more aware of what causes recompiles. The stored procedure shown next causes several compiles due to interleaved DDL and DML and use of SET statements.
SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE PROCEDURE dbo.usp_Recompiles_SeveralTimes AS SET NOCOUNT ON -- Doesn't cause a recompile -- Cause a recompile due to creation of the temporary table CREATE TABLE #Temp1 ([id] int identity (1,1) , [name] varchar(64)) INSERT INTO #Temp1 ([name]) VALUES ('Gregor Samsa') -- Cause a recompile because of a change in a session option SET ANSI_NULLS OFF INSERT INTO #Temp1 ([name]) VALUES ('Padme Amidala') -- Cause a recompile because of a change in a session option SET ANSI_WARNINGS OFF INSERT INTO #Temp1 ([name]) VALUES ('Casper Guttman') -- Cause a recompile because of a new index CREATE CLUSTERED INDEX IDX_Name ON #Temp1 ([name]) SELECT * FROM #Temp1 ORDER BY [name] DROP TABLE #Temp1 -- Doesn't Cause a recompile GO
We will use this stored procedure as an example for how to monitor and diagnose recompiles in the next section.