Recompiles Due to Interleaving DDL and DML Operations
Stored procedures will recompile if 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 has to recompile the stored procedure during execution. This recompilation takes 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 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, like permanent objects, if you change the schema of a temporary table, the 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 run 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).
- 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
- 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 toward creating that trouble-free, lightening-fast database that all of us would love to manage.
Knowledge Base 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
]]>