🗄️ SQL Server: Fix a Temp Table Causing a Plan Recompile Every Call
A stored procedure’s cached execution plan is partly built around the statistics of any #temp table it uses, and SQL Server automatically triggers a recompile whenever those statistics change enough between calls to make the cached plan look unreliable. A temp table that goes from empty at compile time to thousands of rows at execution time crosses that threshold almost every single call, which means the procedure recompiles on every execution instead of reusing a cached plan, even though nothing about the procedure’s actual logic ever changed.
🔎 The Problem
CREATE PROCEDURE dbo.SummarizeOrders
AS
BEGIN
CREATE TABLE #Staging (OrderId INT, Total DECIMAL(10,2));
INSERT INTO #Staging
SELECT OrderId, Total FROM Orders WHERE Status = 'Pending';
-- row count varies from 0 to tens of thousands between calls
SELECT CustomerId, SUM(Total) FROM #Staging s
JOIN Orders o ON o.OrderId = s.OrderId
GROUP BY CustomerId;
END
-- sp_recompile-style statement recompiles fire on nearly every call
-- because #Staging's row count keeps crossing the recompile threshold
✅ Fix: Pick the Tool That Matches How Much the Row Count Actually Varies
- If the procedure is already paying for a recompile every call anyway, make it deliberate with
OPTION (RECOMPILE)on just the statement that needs fresh cardinality estimates, instead of letting the automatic heuristic trigger recompiles you can’t control. - For a procedure where the plan is still mostly good despite minor statistics drift, use
KEEP PLAN(or the looserKEEPFIXED PLAN) to raise the threshold at which a recompile fires, trading a little plan staleness for far fewer recompiles. - When the staging set is reliably small, consider a table variable instead of a temp table — it carries no statistics and therefore never triggers this specific kind of recompile, at the cost of SQL Server always estimating its cardinality as a fixed small number.
🚨 Why This Is Dangerous
- Chasing intermittent slowness in query duration metrics alone, without checking recompile counts in the plan cache, can miss this entirely, since each individual recompile is fast on its own and only adds up to a real cost under high call volume.
A temp table’s row count isn’t just data to the optimizer — it’s a number the optimizer is actively watching, and watching it change enough, enough times, is exactly what a recompile threshold exists to catch.
