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. |
