Do temp tables cause Recompilations in SQL Server?

Question

we have a database on SQL Server 2019 with compatibility set to 110 (SQL Server 2012). We have a stored procedure that is being recompiled frequently, and I noticed we are using many temp tables. We create a temp table, insert data, and then add an index on it for further queries. I suspect this might be causing the recompilations. Do temp tables trigger recompiles, and if so, under what conditions? Thank you!

Answer 1

Scenario 1 (No #temp table caching)

CREATE OR ALTER PROC SomeProc1
AS
CREATE TABLE #my_table(A INT, B INT)
--Recompiled every execution with reason "Deferred Compile"
INSERT #my_table VALUES(1,2);
BeforeSelect1:
--Recompiled every execution with reason "Deferred Compile"
--and then again with reason "Temp Table Changed" when jumped up to by the GOTO and executed for a second time following index creation
SELECT COUNT(*), A FROM #my_table GROUP BY A;
IF NOT EXISTS(SELECT * FROM tempdb.sys.indexes where object_id = object_id('tempdb..#my_table') and index_id <> 0)
    BEGIN
    CREATE INDEX ix ON #my_table(A);
    GOTO BeforeSelect1
    END
--Recompiled every execution with reason "Deferred Compile"
SELECT COUNT(*), A FROM #my_table GROUP BY A;

Tracing the compilation events as described in the Plan Caching and Recompilation in SQL Server 2012 white paper shows exactly the same on each execution.

In the above case the #temp table does not exist at the start of execution on every execution. So all the statements referencing it will be subject to deferred compile and compiled immediately before they are first executed. If you just have a linear flow through the statements then this is the only recompile reason that you will see due to the #temp table usage itself. I added a GOTO above to make things a bit more interesting and to show the second recompile of the first SELECT statement following the index creation.

Scenario 2 (With #temp table caching)

CREATE OR ALTER PROC SomeProc2
AS
CREATE TABLE #my_table(A INT  INDEX ix , B INT)
--Recompiled on first execution with reason "Deferred Compile"
INSERT #my_table VALUES(1,2);
--Recompiled on first execution with reason "Deferred Compile"
SELECT COUNT(*), A FROM #my_table GROUP BY A;
--Recompiled on first execution with reason "Deferred Compile"
SELECT COUNT(*), A FROM #my_table GROUP BY A;

The significant change from the first proc is that there is no separate CREATE INDEX statement. This means that the #temp table is eligible for Temporary Table Caching (as that was the only blocking issue to this in this case) if SQL Server decides to do so (on my dev machine this did take a few attempts to see as I was somewhat starving my local SQL Server of memory). When no cached temp table is reused it shows the usual pattern of deferred compiles. The pink highlighted area in the trace shows an execution where it reused a cached temporary table and there were no recompile events. This doesn't mean that there will always be zero recompilation events associated with the #temp table however. You can still end up seeing usual other recompile reasons such as "Statistics changed" - though how this works in the case of cached temporary tables can be problematic.

Answer 2

The Recompile a Stored Procedure doc says:

Automatic recompiling occurs whenever SQL Server is restarted. It also occurs if an underlying table referenced by the procedure has undergone physical design changes.

Also, the Plan Caching and Recompilation in SQL Server 2012 white paper states that:

Whenever a schema change occurs for any of the objects referenced by a batch, the batch is recompiled. Schema changes are defined as one or more of the following:

• Adding or dropping columns to a table or view.
• Adding or dropping constraints, defaults, or rules to/from a table.
• Adding an index to a table or an indexed view.
• Dropping an index defined on a table or an indexed view. This only applies if the index is used by the query plan in question.
• Dropping (note: not creating or updating) a statistic defined on a table. This only applies if the query plan uses the table. Updating a statistic (both manual and auto-update) may cause an optimality- related (data related) recompilation of any query plans that uses this statistic. Optimality-related recompilations are described later.

Therefore, the culprit is not the temp table itself, but the change made on its schema.