Avoid recompiles of stored procedures


Avoid recompiles of stored procedures : 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 :

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

Khan sql dba – mcitp

Advertisements