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