Minimizing Stored Procedure Recompilation Problems in SQL Server 2000

Reasons That a Stored Procedure is Recompiled

SQL Server recompiles stored procedures for a variety of reasons. Last month's article went into detail about the reasons. In short the reasons amount to:

1. You told it to. There are several ways that you can request a recompile such as using the WITH RECOMPILE clause on the procedure definition.
2. New distribution statistics are generated or a sufficient number of row modifications occur
3. One of these SET statements
   • SET ANSI_DEFAULTS
   • SET ANSI_NULLS
   • SET ANSI_PADDING
   • SET ANSI_WARNINGS
   • SET CONCAT_NULL_YIELDS_NULL
   
    
4. Interleaving SQL DDL and SQL DML operations. For example creation of permanent or temporary tables and the creation of indexes on tables forces a recompile at the next SQL DML statement, such as a SELECT.
5. The plan is aged out of cache.

Let's take a look at each of these reasons for recompile and suggest what to do about it.

Reason 1. You asked SQL Server to Perform a Recompile

The WITH RECOMPILE clause requests that a stored procedure be recompiled every time 'it is used. The reason that you'd use WITH RECOMPILE is that the best plan for the statements in the stored procedure tends to vary depending on the procedure's parameters and caching is causing SQL Server to sometimes use a suboptimal plan.

The best time to use WITH RECOMPILE is when the cost of the recompile is very small compared to the time it takes to run the stored procedure. For example a complex report on a large amount of data. If that's the case then by all means, keep the WITH RECOMPILE clause. Otherwise, you are better off removing the WITH RECOMPILE option.

If there are specific values of the stored procedure's parameters that you know warrant a recompile, you can always put the WITH RECOMPILE option on the EXEC statement. If you do that, you will want to add an sp_recompile after the procedure runs. For example, let's assume that a stored procedure, usp_MyReport, takes a single parameter, @DepartmentNum and that the procedure is run in this way almost all of the time. Sometimes you want a report for the entire organization, not just a department and usp_MyReport accepts NULL for @DepartmentNum. Now let's say that when @DepartmentNum is supplied, the optimal plan is to use a range scan on the non-clustered index that starts with the DepartmentNum column. However, when a report is requested for the entire organization, the optimal plan is a table scan. Instead of invoking usp_MyReport, replace reference to it with references to usp_MyReportInvoker shown here:

CREATE PROCEDURE usp_MyReportInvoker 
 
     @DepartmentNum INT

AS 
    IF @DepartmentNum IS NOT NULL
       EXEC usp_MyReport @DepartmentNum
    ELSE BEGIN
       EXEC usp_MyReport NULL WITH RECOMPILE
       EXEC sp_recompile usp_MyReport
    END

This procedure recompiles usp_MyReport only when you know that changing the plan is going to produce faster results. By doing this, an optimal plan is used every time the report is run. Most of the time, when department number is supplied, SQL Server can reuse the cached plan.

When I was a child, every once in a while as my mother and father would leave the house for an evening out, my mother would turn to my brother, two sisters and me and say, "When I come back, I don't want to find any of you with beans up your nose!" Not that any of use would have thought of putting beans up our nose had she not mentioned it in the first place. It was a reminder not to do things we knew we really shouldn't be doing. In the category of "Don't put beans up your nose," do not use DBCC FREEPROCCACHE, sp_recompile, or WITH RECOMPILE unless you have a real good reason. If you have a good enough reason, you will live with the stored procedure recompiles.

Reason 2: New Distribution Statistics or a Sufficient Number of Row Modifications

SQL Server does recompiles after statistics are generated or after a large enough number of row modifications. It does this because this approach tends to produce better execution plans. It's a good thing. However, there are circumstances where you know that recompiles are not changing the plan because the data is not really changing that much. In these cases you might want to turn off the automatic generation of statistics and update them your self on a schedule that you control.

Reason 3: SET Statements That Change Session Options

To illustrate the causes of stored procedure recompilation including SET statements, let's bring back the stored procedure usp_Recompiles_Several_Times from last month's article. It was specifically constructed to cause recompilation at least four times each call. Here's the script to create it:

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

The best approach to minimize recompiles caused by changing these options is to not change them. Do this by establishing a consistent set of options and always using them. Unfortunately, many code changes may be required by this approach. For example, if you were to always have CONCAT_NULL_YEILDS_NULL set to OFF, you'd have to do additional checking with the ISNULL function or COALESCE on the occasions when you didn't want a NULL result.

If you establish consistent session options at the start of database development you may be able to avoid SET statements. Trying to change the code to use a consistent set of options after it has been written is very difficult. In those cases the best approach would be to minimize the recompiles by grouping SET statements together. That works because the recompile does not happen until the next SQL DML statement. So if our procedure really required that ANSI_NULLS be OFF and ANSI_WARNINGS be OFF it would be best to move them to the start of the procedure so that there would only be one recompile. The first few lines of our sample SP would be:

SET NOCOUNT ON -- Doesn't cause a recompile
-- Cause a recompile because of a change in a session option
SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF
INSERT INTO #Temp1 ([name]) VALUES ('Gregor Samsa')

One recompile is better than two.