Minimizing SQL Server Stored Procedure Recompiles - Page 3

Reason 4: Interleaving DDL and DML Operations.

Once a SQL DDL statement, such as CREATE TABLE, is executed the next SQL DML statement, such as SELECT, that is executed causes the stored procedure to be recompiled before execution is resumed. Stored procedures often contain DDL statements for managing temporary tables. You can reduce the frequency of this type of recompilation through several strategies:

• Group SQL DDL so that only one recompile is produced
• Replace temporary tables with TABLE variables
• Replace temporary tables with permanent tables
• Minimize references to temporary tables created outside the procedure
• Any references to a temporary table should proceed all DROP TABLE statements
• Reuse temporary tables instead of dropping and recreating them inside the same procedure.

Most of these strategies are pretty obvious, but one, using TABLE variables is new to SQL Server 2000 and I'll concentrate on it. TABLE variables are created with a DECLARE statement instead of a CREATE TABLE statement. Here's a sample:

DECLARE @myTable (id int identity(1,1) primary_key
                 , ColumnA varchar(20)
                 , ColumnB varchar(20)
                 )

Once declared, a TABLE variable can be used like other tables. You can execute INSERTs, UPDATEs, DELETEs, and SELECTs against it. The major limitation to TABLE variables is that their scope is limited to the procedure that creates them. They cannot be referenced by or pass to any stored procedure, user-defined function, or trigger that is invoked by the procedure that DECLAREs them.

Another limitation of TABLE variables is that you cannot create indexes on them. The only indexes they have are the ones that SQL Server creates implicitly when the variable has a primary_key or a unique constraint.

The limitations on TABLE variables may make it impossible to use them. SQL Server actually creates a special kind of table for them in tempdb. However, it does not put any information about them into tempdb's system tables and they end up consuming fewer resources than a temp table would.

In the sample stored procedure replacing the temporary table with a TABLE variable allows us to eliminate the CREATE TABLE and the CREATE INDEX statements. These changes cut the number of recompiles down to one. Here is the procedure with the SET statements grouped at the beginning of the procedure and #Temp1 converted to a TABLE variable:

CREATE PROCEDURE dbo.usp_Recompiles_Just_Once AS 

DECLARE @Temp1 TABLE ([id] int identity (1,1)
                   , [name] varchar(64))

SET NOCOUNT ON -- Doesn't cause a recompile
-- Cause a recompile because of a change in a session option
SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF

INSERT INTO @Temp1 ([name]) VALUES ('Gregor Samsa')
INSERT INTO @Temp1 ([name]) VALUES ('Padme Amidala')
INSERT INTO @Temp1 ([name]) VALUES ('Casper Guttman')

SELECT * FROM @Temp1 ORDER BY [name]

GO

There is still one recompile every time the procedure is executed but one is better than four.

Reason 5: The Plan is Aged Out of Cache

Plans are aged out of cache when they are either not used or when SQL Server is low on memory. If you have supplied your server with adequate memory, this should not happen until long after the stored procedure was last used. If it does, you should examine the overall memory situation instead of focusing on the recompiles.

Conclusion

Although stored procedure recompiles can be a performance problem this article has shown you several ways to minimize their frequency. As with all good things, there is a point of diminishing returns. This is particularly true of recompiles caused by data modifications. SQL Server 2000 uses a strategy of replacing execution plans fairly frequently. That is the only way that the cost based optimizer can have an impact on performance.

» See All Articles by Columnist Andrew Novick