Home
About me and Nucleus
Partners
Links
Articles, tips etc.
My blog
Microsoft articles
Forums
Course demo files
Sync logins
SMTP Alerter
Using stored procedures is generally considered a good thing. One advantage of stored procedures is that they are precompiled. This means that at execution time, SQL Server will fetch the precompiled procedure plan from cache memory (if exists) and execute it. This is generally faster than optimizing and compiling the code for each execution. However, under some circumstances, a procedure needs to be recompiled during execution.
Thanks to SQL Server MVP Frank Kalis, this article is translated to German.
More information
The execution engine can come to a point in the stored procedure where it finds that it needs to recompile the whole procedure. Note that stored procedures are not compiled statement by statement. If a recompile is needed, say, at the end of the procedure, then the whole procedure will be optimized and compiled. Why does this happen? Here are some reasons:
- SQL Server accesses a table and notices that there are radically different numbers of rows in the table compared to when the procedure plan was created. The KEEP PLAN and KEEPFIXED PLAN hints can be helpful in this scenario.
- DDL after DML. Say that you create a temp table and create an index on the temp table. This has not happened yet when the procedure is initially compiled. So, SQL Server can decide to recompile the procedure during execution when it comes to a later SELECT statement which uses the temp table in order to evaluate optimal execution plan for that SELECT.
- You have a SET statement in the procedure which changes the way SQL Server processes the following statements in the procedure. This is what this article addresses.
Be aware that a procedure can be recompiled several times during execution. I recommend that you use Profiler and catch the SP:Recompile event to determine whether your procedure is recompiled at execute time. You will get one event for each re-compilation.
Not all SET options causes recompiles. So I conducted a test to find out which does. Below table lists all documented SET options and whether each causes a recompile or not.
| Option | Causes recompile | Comment |
| SET ANSI_DEFAULTS OFF | yes | |
| SET ANSI_NULLS OFF | yes | Causes recompile even though it is a dummy operation |
| SET ANSI_PADDING OFF | yes | |
| SET ANSI_WARNINGS OFF | yes | |
| SET ARITHABORT OFF | yes | |
| SET CONCAT_NULL_YIELDS_NULL OFF | yes | Use ISNULL() or COALESCE instead. |
| SET DATEFORMAT dmy | yes | Use a language neutral date format instead of this option. |
| SET FORCEPLAN ON | yes | |
| SET LANGUAGE svenska | yes | Use a language neutral date format instead of this option. |
| SET NUMERIC_ROUNDABORT ON | yes | |
| SET ARITHIGNORE ON | no | |
| SET CURSOR_CLOSE_ON_COMMIT ON | no | |
| SET DATEFIRST 2 | no | |
| SET DEADLOCK_PRIORITY LOW | no | |
| SET DISABLE_DEF_CNST_CHK ON | no | |
| SET IMPLICIT_TRANSACTIONS ON | no | |
| SET LOCK_TIMEOUT 200 | no | |
| SET NOCOUNT ON | no | |
| SET QUERY_GOVERNOR_COST_LIMIT 30 | no | |
| SET REMOTE_PROC_TRANSACTIONS ON | no | |
| SET ROWCOUNT 30 | no | |
| SET TEXTSIZE 3000 | no | |
| SET TRANSACTION ISOLATION LEVEL REPEATABLE READ | no | |
| SET XACT_ABORT ON | no | |
| SET IDENTITY_INSERT ON | n/a | Did not test. |
| SET QUOTED_IDENTIFIER | n/a | Setting inherited from CREATE time. Did not test. |
| SET FMTONLY | n/a | Odd option. Did not test. |
| SET NOEXEC | n/a | Odd option. Did not test. |
| SET PARSEONLY | n/a | Odd option. Did not test. |
| SET SHOWPLAN_ALL | n/a | Odd option. Did not test. |
| SET SHOWPLAN_TEXT | n/a | Odd option. Did not test. |
| SET STATISTICS IO | n/a | Odd option. Did not test. |
| SET STATISTICS PROFILER | n/a | Odd option. Did not test. |
| SET STATISTICS TIME | n/a | Odd option. Did not test. |
| SET ANSI_NULL_DFLT_OFF | n/a | Odd option. Did not test. |
| SET ANSI_NULL_DFLT_ON | n/a | Odd option. Did not test. |
| SET FIPS_FLAGGER | n/a | Odd option. Did not test. |
SQL Code
I used below SQL code to semi-automate above work:
USE pubs
SET NOCOUNT ON
GO
--Create dummy proc so we can do ALTER PROC when we generate the proc
CREATE PROC p AS
PRINT 'dummy'
GO
--Create table to hold the SET parameters to set:
CREATE TABLE ct (cn VARCHAR(100) PRIMARY KEY)
--Populate the table
INSERT ct VALUES('SET DATEFIRST 2')
INSERT ct VALUES('SET DATEFORMAT dmy')
INSERT ct VALUES('SET DEADLOCK_PRIORITY LOW')
INSERT ct VALUES('SET LOCK_TIMEOUT 200')
INSERT ct VALUES('SET CONCAT_NULL_YIELDS_NULL OFF')
INSERT ct VALUES('SET CURSOR_CLOSE_ON_COMMIT ON')
INSERT ct VALUES('SET DISABLE_DEF_CNST_CHK ON')
INSERT ct VALUES('SET LANGUAGE svenska')
INSERT ct VALUES('SET ARITHABORT OFF')
INSERT ct VALUES('SET ARITHIGNORE ON')
INSERT ct VALUES('SET ANSI_NULLS OFF')
INSERT ct VALUES('SET NOCOUNT OFF')
INSERT ct VALUES('SET NUMERIC_ROUNDABORT ON')
INSERT ct VALUES('SET QUERY_GOVERNOR_COST_LIMIT 30')
INSERT ct VALUES('SET ROWCOUNT 30')
INSERT ct VALUES('SET TEXTSIZE 3000')
INSERT ct VALUES('SET ANSI_DEFAULTS OFF')
INSERT ct VALUES('SET ANSI_PADDING OFF')
INSERT ct VALUES('SET ANSI_WARNINGS OFF')
INSERT ct VALUES('SET FORCEPLAN ON')
INSERT ct VALUES('SET IMPLICIT_TRANSACTIONS ON')
INSERT ct VALUES('SET REMOTE_PROC_TRANSACTIONS ON')
INSERT ct VALUES('SET TRANSACTION ISOLATION LEVEL READ COMMITTED')
INSERT ct VALUES('SET XACT_ABORT ON')
--Create the proc which in turns create a proc with the various SET parameters inside.
--Then debug this proc, set a breakpoint and execute the proc named "p" for
--each iteration and see if it triggered a recompile.
CREATE PROC generate_proc AS
DECLARE @sql NVARCHAR(2000), @set_option NVARCHAR(100)
DECLARE c CURSOR FOR SELECT cn FROM ct ORDER BY cn
OPEN c
FETCH NEXT FROM c INTO @set_option
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER PROC p AS' + CHAR(13) + CHAR(10)
SET @sql = @sql + @set_option + CHAR(13) + CHAR(10)
SET @sql = @sql + 'SELECT au_fname, au_lname FROM authors WHERE au_lname = ''White''' + CHAR(13) + CHAR(10)
PRINT @set_option
EXEC(@sql)
FETCH NEXT FROM c INTO @set_option
END
CLOSE c
DEALLOCATE c