Identifying Stored Procedure Recompilation Problems in SQL Server 2000

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:

1. Use of the WITH RECOMPILE option on the CREATE PROC or ALTER PROC statement. The May 6^th 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
   
   
2. Executing the system stored procedure sp_recompile on the procedure or on a table or view that the procedure depends on.
   
   
3. Use of the WITH RECOMPILE option on the EXEC statement
   
   
4. 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:

5. 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.
   
   
6. 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.
   
   
7. 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:

8. 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:

9. 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.
   
   
10. 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.