Optimizing Stored Procedures To Avoid Recompiles

, 2002-02-26

One of the most overlooked areas in optimizing SQL Server and Transact-SQL is the recompilations of stored

procedures. Recently started looking at this issue where I worked and stored procedures gathered from other clients and

I noticed that there is an issue with SQL developers not taking recompiles into account when they create new code. When

a stored procedure recompiles it will place a compile lock on the objects referenced by the stored procedure and if there

are enough stored procedure recompiles the database may experience blocking. While all databases will experience stored

procedure recompiles as a normal matter of database operations, it is when a stored procedure recompiles with every run

that a database administrator or Transact-SQL developer needs to look out for and determine a remedy. A database getting

thousands of recompiles an hour will suffer in performance and show short term blocking that will affect the database

users. Will the query optimizer in SQL Server 2000 seems to have been greatly enhanced in its ability to reuse cached

procedure plans, a few of the following problems will still show up in SQL Server 2000.

Reasons stored procedures recompile

Stored procedures will normally recompile before execution for a number of reasons. Dropping and recreating the stored

procedure, using the WITH RECOMPILE clause in the CREATE PROCEDURE or the EXECUTE statement, change the schema of any

referenced objects, running the sp_recompile system stored procedure against a table referenced by the stored procedure,

restoring the database containing the stored procedure or any object referenced by the stored procedure, or the stored

procedures plan dropping from the cache. While these recompilations are normal and cannot be helped, DBAs and developers

should not assume that all stored procedure recompiles are for normal reasons and should take a proactive approach to

determine if they have a recompile problem.

Using Profiler to capture recompiles

With the use of SQL Profiler it is very easy for a DBA or developer to determine if a stored procedure abnormally

recompiles before it is ever put into a test or production environment. It is also very easy for production DBAs to

determine if they have a problem with stored procedures already in production.

To determine if you have a problem with existing stored procedures or a specific stored procedure:

1. Start Profiler

2. Start a new trace

3. Connect to your server

4. On the General Tab, give the trace a name

5. On the Events Tab remove all default events and add SP:Recompile, SP:Starting, and SP:Completed under Stored

Procedure events. If you want to determine the statement that causes the recompile also add SP:StmtStarting and

SP:StmtCompleted to the selection.

6. You can leave the data columns as is or change them as you see fit. You can also leave the trace without filters,

but stored procedures run by replication may tend to clutter your trace. If you tracing only one stored procedure,

you can filter by the stored procedure name under the Text-Like filter.

Example of a stored procedure which will recompile on every run (7.0 and 2000)

USE pubs

GO

IF OBJECT_ID('dbo.spShowRecompile') IS NOT NULL

DROP PROCEDURE dbo.spShowRecompile

GO

CREATE PROCEDURE dbo.spShowRecompile

AS

SET NOCOUNT ON

DECLARE @lngCounter INTEGER

SET @lngCounter = 1

--create temp table

CREATE TABLE #tTemp

(a INTEGER, b INTEGER)

SELECT count(*) FROM #tTemp

--add large amount of rows to table

WHILE @lngCounter < 2000 BEGIN INSERT INTO #tTemp(a) VALUES(@lngCounter) SET @lngCounter = @lngCounter + 1 END --Create index on temp table CREATE CLUSTERED INDEX ind_temp ON #tTemp(a) SELECT count(*) FROM #tTemp GO EXEC dbo.spShowRecompile Let the trace run and look for stored procedures that recompile multiple times in a row or that recompile after they have started. To determine the statement causing the recompile look at the statement immediately before and after the recompile if you included SP:StmtStarting and SP:StmtCompleted in your trace. Now that you have determined which stored procedures are recompiling abnormally and which statements are causing the recompiles, we can look at ways to change the stored procedure code to stop the recompiles. Reducing COMPILE locks

It is considered a good practice to reference all objects in stored procedure code with the owner's name. While this will

not stop recompiles, it will stop SQL Server from placing a COMPILE lock on the procedure will it determines if all objects

referenced in the code have the same owners as the objects in the current cached procedure plan.

Example to show qualifying objects with their owners (7.0 and 2000)

USE pubs

GO

IF OBJECT_ID('dbo.spShowOwnersName') IS NOT NULL

DROP PROCEDURE dbo.spShowOwnersName

GO

CREATE PROCEDURE dbo.spShowOwnersName

AS

SELECT * FROM dbo.authors

GO

EXEC dbo.spShowOwnersName

Recompiles due to row modifications

Stored procedure will recompile is that 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. You will notice this problem quite often when you are working with temporary tables in SQL Server 7.0 as

SQL Server will determine that after 6 modifications to a temporary table any stored procedure referencing that table will

need to be recompiled.

Example of row modifications causing recompile (7.0)

IF OBJECT_ID('dbo.spShowRecompile') IS NOT NULL

DROP PROCEDURE dbo.spShowRecompile

GO

CREATE PROCEDURE dbo.spShowRecompile

AS

SET NOCOUNT ON

DECLARE @lngCounter INTEGER

SET @lngCounter = 1

CREATE TABLE #Temp

(

lngID INTEGER

)

WHILE @lngCounter < 2000 BEGIN INSERT INTO #Temp VALUES(@lngCounter) SET @lngCounter = @lngCounter + 1 END SELECT COUNT(*) FROM #Temp GO. There are several ways to avoid this specific recompile. Luckily you have two paths to take: using sp_executesql and using the KEEPFIXED PLAN query hint that is found in SQL Server 7.0 SP3 and SQL Server 2000. You can find information in BOL on sp_executesql and in case you don't have SQL Server 7.0 SP3 or SQL Server 2000 here is what BOL says about KEEPFIXED PLAN: "KEEPFIXED PLAN Forces the query optimizer not to recompile a query due to changes in statistics or to the indexed column (update, delete, or insert). Specifying KEEPFIXED PLAN ensures that a query will be recompiled only if the schema of the underlying tables is changed or sp_recompile is executed against those tables." Books Online article on the OPTION clause. The following examples will show you the difference both sp_executesql and KEEPFIXED PLAN will have on avoiding stored procedure recompiles. Example of using sp_executesql to avoid recompiles from row modifications (7.0)

IF OBJECT_ID('dbo.spShowRecompile') IS NOT NULL

DROP PROCEDURE dbo.spShowRecompile

GO

CREATE PROCEDURE dbo.spShowRecompile

AS

SET NOCOUNT ON

DECLARE @lngCounter INTEGER

SET @lngCounter = 1

CREATE TABLE #Temp

(

lngID INTEGER

)

WHILE @lngCounter < 2000 BEGIN INSERT INTO #Temp VALUES(@lngCounter) SET @lngCounter = @lngCounter + 1 END EXEC dbo.sp_executesql N'SELECT COUNT(*) FROM #Temp' GO Example of using KEEPFIXED PLAN option to avoid recompiles (7.0 SP3)

IF OBJECT_ID('dbo.spShowRecompile') IS NOT NULL

DROP PROCEDURE dbo.spShowRecompile

GO

CREATE PROCEDURE dbo.spShowRecompile

AS

SET NOCOUNT ON

DECLARE @lngCounter INTEGER

SET @lngCounter = 1

CREATE TABLE #Temp

(

lngID INTEGER

)

WHILE @lngCounter < 2000 BEGIN INSERT INTO #Temp VALUES(@lngCounter) SET @lngCounter = @lngCounter + 1 END SELECT COUNT(*) FROM #Temp OPTION (KEEPFIXED PLAN) GO Recompiles due to interleaving DDL and DML operations

Stored procedures will recompile is that 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 reason this happens is that the temporary objects due not exist when the initial compilation of the code takes

place so SQL Server will have to recompile the stored procedure during execution. This recompilation will take place after

the temporary object is referenced for the first time. By placing all of your temporary table creation statements together,

SQL Server can create plans for those temporary tables when one of them is referenced for the first time. This recompile

will still take place during the execution of the stored procedure, but you have cut down of the recompiles from n to two

(one for the stored procedure and one when the first reference to a temporary table is made). SQL Server will also be able

to reuse the execution plan for the stored procedure the next time the procedure is called and your recompiles will go to

zero. Remember that like permanent objects, if you change the schema of a temporary table, that change will cause the

stored procedure to recompile as well. Make all schema changes (such as index creation) right after your create table

statements and before you reference any of the temporary tables. If you take the stored procedure created during the

section on using Profiler and modify it as written below you will stop the unnecessary recompiles.

Modifications to stop the recompile (7.0 and 2000)

USE pubs

GO

IF OBJECT_ID('dbo.spShowRecompile') IS NOT NULL

DROP PROCEDURE dbo.spShowRecompile

GO

CREATE PROCEDURE dbo.spShowRecompile

AS

SET NOCOUNT ON

DECLARE @lngCounter INTEGER

SET @lngCounter = 1

--create temp table

CREATE TABLE #tTemp

(a INTEGER, b INTEGER)

--Create index on temp table

CREATE CLUSTERED INDEX ind_temp ON #tTemp(a)

SELECT count(*) FROM #tTemp

--add large amount of rows to table

WHILE @lngCounter < 2000 BEGIN INSERT INTO #tTemp(a) VALUES(@lngCounter) SET @lngCounter = @lngCounter + 1 END SELECT count(*) FROM #tTemp GO EXEC dbo.spShowRecompile Recompiles due to operations against temporary objects

SQL Server will recompile a stored procedure every time it is ran if any of the following conditions apply in that stored

procedure: If statements that contain the name of a temporary table refer to a table created by a calling or called stored

procedure or in a string execute by using sp_executesql or the EXECUTE statement. If any statement that contains the

name of the temporary table appear syntactically before the temporary table is created in the stored procedure or trigger.

If there are any DECLARE CURSOR statements whose SELECT statement references a temporary table. If any statements

that contain the name of a temporary table appear syntactically after a DROP TABLE against the temporary table (you

might read that DROP TABLES for temporary tables are not needed since they are dropped at the conclusion of the stored

procedure execute, but it is a good idea to drop temporary tables as you are done with them to free up system resources).

Or if any statement that creates a temporary table appear in a control-of-flow statement. By avoiding these conditions

when you create your code you can avoid needless stored procedure recompiles.

The following SET options are ON by default in SQL Server and changing the state of these options in your stored procedure

will cause the stored procedure to recompile: SET ANSI_DEFAULTS, SET ANSI_NULLS, SET ANSI_PADDING,

SET ANSI_WARNINGS, and SET CONCAT_NULL_YIELDS_NULL. While there are not good workarounds for the first four

SET options, you can work around the last one: SET CONCAT_NULL_YIELDS_NULL by using the ISNULL function found

in Transact-SQL. By simply using the ISNULL function and setting any data that might contain a NULL to an empty string

you can avoid the setting of CONCAT_NULL_YIELDS_NULL in your stored procedure and avoid another unnecessary

stored procedure recompilation.

Example of SET CONCAT_NULL_YIELDS_NULL causing recompile (7.0 and 2000)

USE pubs

GO

IF OBJECT_ID('dbo.spShowRecompile') IS NOT NULL

DROP PROCEDURE dbo.spShowRecompile

GO

CREATE PROCEDURE dbo.spShowRecompile

AS

SET NOCOUNT ON

SET CONCAT_NULL_YIELDS_NULL OFF

SELECT 'Will not showup' + NULL

GO

EXEC dbo.spShowRecompile

Summary

While most of these problems occur on a larger scale in SQL Server 7.0, they can still be a problem in SQL Server 2000.

By checking for stored procedure recompiles during the development of new Transact-SQL code and running a health-check

on your existing stored procedures for recompilations, you can help to optimize your database performance by lowering the

risk of blocking while a stored procedure is being recompiled and by improving the overall performance of your stored

procedures by not having them constantly being recompiled. Keeping the above recommendations in mind as you create

your code or optimize existing code will go a long way on creating that trouble-free, lightening-fast database that all of

us would love to manage.

Knowledge Based Articles

Q243586 INF: Troubleshooting Stored Procedure Recompilation

Q276220 INF: How to Use KEEPFIXED PLAN to Disable Stored Procedure Recompilations

Q294942 PRB: SET CONCAT_NULL_YIELDS_NULL May Cause Stored Procedures to Recompile

Q250506 FIX: Concurrent Execution of Stored Procedure That Recompiles May Fail To Execute All Statements

Q263889 INF: SQL Blocking Due to [[COMPILE]] Locks

Copyright 2002 by Randy Dyess, All rights Reserved

Rate

3.67 (3)

Share

Share

Rate

3.67 (3)

Related content

Making Dynamic Queries Static

Building and executing dynamic sql in a stored procedure - is it the only way to solve problems like supporting a simple search function? Leon offers a couple alternatives that let you continue to provide the functionality in a stored procedure without using dynamic sql. Interesting ideas worth exploring!

1 (2)

2002-05-02

12,884 reads

SQL Server 2000 Table Hints

As you advance in your skills as a Transact-SQL developer or SQL Server database administrator there will come a time when you need to override SQL Server's locking scheme and force a particular range of locks on a table. This article by Randy Dyess shows you how to optimize your queries using table hints.

3 (2)

2002-04-15

21,272 reads