Database Reference
In-Depth Information
You can see that the query is recompiled when executed for the first time. The cause of the recompilation, as
indicated by the corresponding recompile_cause value, is the same as the cause of the recompilation on a regular
table. However, note that when the stored procedure is reexecuted, it isn't recompiled, unlike the case with a regular
table.
The schema of a local temporary table during subsequent execution of the stored procedure remains the same
as during the previous execution. A local temporary table isn't available outside the scope of the stored procedure,
so its schema can't be altered in any way between multiple executions. Thus, SQL Server safely reuses the existing
plan (based on the previous instance of the local temporary table) during the subsequent execution of the stored
procedure and thereby avoids the recompilation.
to avoid recompilation, it makes sense to hold the intermediate result sets in the stored procedure using local
temporary tables, instead of using temporarily created regular tables. But, this makes sense only if you can avoid data
skew, which could lead to other bad plans. in that case, the recompile might be less painful.
Note
SET Options Changes
The execution plan of a stored procedure is dependent on the environment settings. If the environment settings
are changed within a stored procedure, then SQL Server recompiles the queries on every execution. For example,
consider the following code:
IF (SELECT OBJECT_ID('dbo.TestProc')
) IS NOT NULL
DROP PROC dbo.TestProc;
GO
CREATE PROC dbo.TestProc
AS
SELECT 'a' + NULL + 'b'; --1st
SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT 'a' + NULL + 'b'; --2nd
SET ANSI_NULLS OFF;
SELECT 'a' + NULL + 'b';
--3rd
GO
EXEC dbo.TestProc; --First execution
EXEC dbo.TestProc; --Second execution
Changing the SET options in the stored procedure causes SQL Server to recompile the stored procedure before
executing the statement after the SET statement. Thus, this stored procedure is recompiled twice: once before
executing the second SELECT statement and once before executing the third SELECT statement. The Extended Events
output in Figure 17-11 shows this.
 
 
Search WWH ::




Custom Search