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
Any references to a temporary table should proceed all DROP TABLE
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
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]
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.
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