Optimizing SQL Server Stored Procedures to Avoid Recompiles

Recompiles Due to Row Modifications

Stored procedure will recompile if there is 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."

The following examples will show you the difference both sp_executesql and KEEPFIXED PLAN will make 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