What kinds of operations could cause a "Temp table changed" recompilation?

Question

Frequent recompilation happens in one SP with 'Temp table changed' cause. What kinds of operations on temp table would trigger this? There are no alter and create index operations on temp table, just DMLs like 'Statistics changed' as below. Thanks.

Answer 1

What kinds of operations on temp table would trigger this?

According to Plan Caching and Recompilation in SQL Server 2012, the explanation is:

SubclassName	SubclassValue	Detailed reason for recompilation
Temp table changed	5	Temp table schema, binding, or permission changed.

If you're not changing the schema, binding, or permissions another explanation is that SQL Server doesn't trust the schema not to have changed because the temporary table was created in a different scope. Note this includes DML in dynamic SQL where the temporary table was created before running the dynamic SQL.

See Reduced recompilations for workloads using temporary tables across multiple scopes by Joe Sack.

When referencing a temporary table with a DML statement (SELECT, INSERT, UPDATE, DELETE), if the temporary table was created by an outer scope batch, we will recompile the DML statement each time it is executed.

You'll need to be on SQL Server 2019 or higher to benefit from that.