Whether using temporary tables in SQL Server stored procedures will result in recompilation

Once seen on the network, the use of temporary tables in SQL Server's stored procedures can cause the execution plan to not be reused.

The runtime will lead to the recompilation of such a statement, in private to do the test, according to the profile of the tracking results,

If the recompilation is not caused by a change in the statistics, the use of temporary tables alone does not result in recompilation.

But for some special situations, there is a real recompilation,

In order to clarify this problem, consult a large number of data, before the problem is clear, here deliberately recorded, hoping to arbitrarily think that the use of temporary tables in the stored procedure will lead to the re-compilation of this view is corrected.

First, we know that one of the factors that causes temporary table recompilation is the change in statistics, which depends on the amount of data written to the staging table.

Preferred I want to control the amount of data that is inserted in the staging table does not exceed the threshold for recompilation of statistics, excluding the change in statistics leading to recompilation,

See if the SP is running more than once, because a temporary table in the stored procedure produces a recompile

--Preferred to create a table for testing in stored procedures using CREATE TABLE test1 (ID int identity (first), name varchar (50))--Inserts 10,000 test data insert into test1 values ( NEWID ()) Go 10000--creates a stored procedure that defines a temporary table in the stored procedure, writes data to the staging table according to the parameters create proc testrecompile (@i int) asbegincreate table #t (ID Int,name varchar) INSERT INTO #t select Id,name from test1 where id< @iselect * from #tend

Then start running the SP, then monitor the profile to see if the first run, and in addition to the first run, there is no recompilation

--First run, substituting parameter 1exec testrecompile second run, substituting parameter 2exec testrecompile 2

The following is the profile, it can be clearly seen that after the first run, again when running the SP, there is no recompilation action, that is, reusing the first execution plan cache

Here are two questions to explain,

1, when the first run, why not because of the schema changes caused by the recompilation, but deferred Complie?

2, the second run, why did not recompile, because the temporary table is created every time you run the AH, it must have changed the schema (change schema), why not recompile?

First, explain the first question,

1, the first time you run, when the stored procedure testrecompile compiled ,

Insert statement (INSERT INTO #t select Id,name from Test1 where id<@i) and query statement (SELECT * from #t),

Because the #t table has not been created, because the two sentences have not been compiled,

The execution plan at compile time is not fully completed,

　　When this stored procedure executes, the temporary table is created, at which point the statement of the temporary Table object is actually started, and the compilation process is done at the time of execution, not the pure compilation phase.

so this is deferred Complie, This is the compile that is performed at run time, which is called deferred compilation (Deferred Complie).

2, the second problem, re-run the temp table when, as a matter of fact, because the temporary table created, will inevitably lead to schema changes, why did not recompile?

This is because a temporary table is used in the stored procedure, and the use of the temporary table refers to its "name" (#t, for example), rather than the ID (querying sys.sysobjects from the staging database)

Although multiple sessions run this SP at the same time, each session generates a temporary table with different IDs for the temporary tables generated by each session.

Note, however, that the stored procedure does not directly use the ID of the temporary table object, but rather the temporary table name itself.

After the first run, the cache execution plan is the same as the second run, so the second run of the SP can reuse this first generated execution plan.

It says, in some cases, the use of temporary tables in stored procedures can lead to recompilation, and under what circumstances does this happen?

Because in some cases, to generate a temporary table, and then to execute a dynamic SQL to participate in a temporary table of SQL, when the reference to the temporary table is to reference its ID, rather than the name

This is due to the way the temporary table is called, when a temporary table is defined in the stored procedure, when called by exec or sp_executesql, the two methods of executing SQL are equivalent to creating a new session.

At this point, the ID generated by the same temporary table is different because of the different reply, which causes the Sechme change in the stored procedure to be recompiled

On the Code

Create proc testRecompile2 (@i int) asbegincreate table #t (ID int,name varchar) INSERT INTO #t select Id,name from Test1 where Id<[email protected]exec (' select * from #t ') enddbcc freeproccache--first run, substituting parameter 1exec testRecompile2 second run, Substituting parameter 2exec testRecompile2 2

When a temporary table is created in the stored procedure, and the SQL of the temporary table is executed in an exec or sp_executesql manner, the recompilation caused by the schema change will occur.

Because these two ways of executing SQL, the equivalent of a new session to execute SQL, the reference to the temporary table is the ID generated by the Reference temporary table, the ID of the temporary table object between different sessions is different, so the execution plan cannot be reused, the recompilation will occur

In addition, for the recompilation of statistical information changes, it is not much to say, this will not only occur on the temporary table, the ordinary physical table will also be due to the statistical information changes resulting in recompilation, more than a temporary table, the only difference is that the temporary table and the physical table statistics change the threshold value is not the same

We know

This is also easy to verify that the thresholds updated by the temporal table statistics depend on the amplitude of the data in the temporary table, which is the following value

If N < 6, recompilation threshold = 6.

If 6 <= N <=, recompilation threshold = 500.

If n >, recompilation threshold = + 0.20 * N.

Also take the first Test SP as an example, two times the following SP is executed,

--First run, substituting parameter 1
EXEC testrecompile 1
--second run, substituting parameter 2
EXEC testrecompile 2
Because the first parameter 1, the second parameter is 2, does not meet if n < 6, recompilation threshold = 6. So no recompilation due to statistical changes
But when exec Testrecompile 8 is executed, this threshold is reached, If N < 6, recompilation threshold = 6.SP will be recompiled because of statistical changes, this is very simple, I will not do

Whether using temporary tables in SQL Server stored procedures will result in recompilation