Bits of .NET

🗄️ The Stored Procedure That Recompiles Itself Almost Every Time It Runs

A stored procedure that creates a `#temp` table, inserts a meaningful number of rows into it, and then queries it can trigger SQL Server’s automatic recompilation logic FAR more often than expected – because a significant enough change in a temp table’s row count between statements is one of the specific conditions SQL Server watches for to decide the cached execution plan might no longer be a good fit, and it silently recompiles rather than risk running a stale plan.

🔎 The Problem

CREATE PROCEDURE dbo.ProcessDailyOrders
AS
BEGIN
    CREATE TABLE #StagingOrders (OrderId INT, Total DECIMAL(10,2));

    INSERT INTO #StagingOrders
    SELECT OrderId, Total FROM Orders WHERE OrderDate = CAST(GETDATE() AS DATE);

    -- On a busy day, this INSERT can add thousands of rows to a table
    -- that started empty - a large enough jump in row count (governed by
    -- SQL Server'"'"'s internal recompilation thresholds) marks the cached
    -- plan for the queries below as stale, triggering a recompile on
    -- THIS query, and again on every subsequent query touching
    -- #StagingOrders in the same procedure, every single execution.
    SELECT * FROM #StagingOrders WHERE Total > 1000;
END

✅ Fix: Reduce How Much the Temp Table’s Statistics Actually Change

• `SELECT * FROM sys.dm_exec_query_stats` joined with `sys.dm_exec_sql_text`, or a Query Store recompile-reason report, confirms whether `#temp` table cardinality changes are actually the trigger here before spending time on a fix aimed at the wrong cause.
• Adding `OPTION (RECOMPILE)` to the specific statement(s) touching the temp table can actually be the right call here – it accepts a small, predictable recompile cost on every execution in exchange for a plan tailored to the CURRENT row count, instead of an unpredictable recompile storm triggered by whatever threshold SQL Server hits.
• Table variables (`DECLARE @StagingOrders TABLE (…)`) don’t trigger this specific automatic-recompilation behavior the way `#temp` tables do – though they come with their own tradeoff (no statistics at all, which can produce a poor plan for a large result set), so this swap is worth testing rather than assuming it’s a strict improvement.

⚠️ Why the Slowdown Looks Random From the Outside

• The procedure runs identical logic every time, so a sudden slowdown with no code change looks like a mysterious performance regression – the actual trigger is data volume crossing an internal threshold, which has nothing to do with anything a developer directly controls in the query text.
• This is worth checking specifically on procedures that behave inconsistently – fast on light days, slow on heavy ones – since that exact pattern (correlating with data volume rather than code changes) is a strong signal pointing at recompilation rather than a genuine query plan regression.