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