Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 9, 2003

Minimizing SQL Server Stored Procedure Recompiles - Page 2

By Andrew Novick

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


  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

    IF @DepartmentNum IS NOT NULL
       EXEC usp_MyReport @DepartmentNum
       EXEC sp_recompile usp_MyReport

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.

Exactly when statistics get generated and the instructions to turn automatic generation on or off are well documented in Microsoft Knowledge Base article 195565. I refer you there if you're interested. You can read it at: http://support.microsoft.com/default.aspx?scid=kb;EN-US;195565

Reason 3: SET Statements That Change Session Options

Changing the value of five session options with the SET statement cause a recompile. The options are: ANSI_DEFAULTS, ANSI_WARNINGS, ANSI_PADDING, CONCAT_NULL_YEILDS_NULL.

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:


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
INSERT INTO #Temp1 ([name]) VALUES ('Padme Amidala')

-- Cause a recompile because of a change in a session option
INSERT INTO #Temp1 ([name]) VALUES ('Casper Guttman')

-- Cause a recompile because of a new index
SELECT * FROM #Temp1 ORDER BY [name]

DROP TABLE #Temp1 -- Doesn't Cause a recompile

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
INSERT INTO #Temp1 ([name]) VALUES ('Gregor Samsa')

One recompile is better than two.

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.