Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL Server: Fix a Temp Table Causing a Plan Recompile Every Call

- 18.11.26 - ErcanOPAK

🗄️ 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 looser KEEPFIXED 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.

— Database Engineer

Related posts:

SQL Server: Why COUNT(*) and COUNT(column) Can Return Different Numbers

SQL Server: Why COUNT(*) and COUNT(column) Can Return Different Numbers

SQL: Use Recursive CTE to Query Hierarchical Data (Employee Trees)

SQL: Use Recursive CTE to Query Hierarchical Data (Employee Trees)

SQL Server: Fix a Query That Times Out Only When Run Through a Linked Server

SQL Server: Fix a Query That Times Out Only When Run Through a Linked Server

Views: 2

Post navigation

Asp.Net Core: Fix JSON Source Generation Skipping a Derived Type
C#: Fix a Deconstruct Method Never Matching a Pattern Switch

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,059)
  • Get the User Name and Domain Name from an Email Address in SQL (983)
  • How to add default value for Entity Framework migrations for DateTime and Bool (981)
  • Get the First and Last Word from a String or Sentence in SQL (861)
  • How to select distinct rows in a datatable in C# (856)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (658)
  • Add Constraint to SQL Table to ensure email contains @ (613)
  • Average of all values in a column that are not zero in SQL (571)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (549)
  • Find numbers with more than two decimal places in SQL (477)

Recent Posts

  • Visual Studio: Fix Quick Actions Hiding Behind a Stale Squiggle
  • Photoshop: Fix a Smart Filter Resetting After You Relink It
  • Ajax: Fix a Cancelled Request Still Updating Stale UI State
  • C#: Fix a Span Captured by a Lambda Throwing at Runtime
  • The AI Prompt That Converts a REST Endpoint Into a Typed SDK
  • Git: Fix a Worktree Locking a Branch Needed Somewhere Else
  • Asp.Net Core: Fix a Background Service Leaking Scoped Dependencies
  • SQL Server: Fix a Columnstore Index Fragmenting After Bulk Deletes
  • Docker: Fix a Container Losing Logs After a Restart Policy
  • Asp.Net Core: Fix Output Caching Serving a Stale Authorized Page

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,059)
  • Get the User Name and Domain Name from an Email Address in SQL (983)
  • How to add default value for Entity Framework migrations for DateTime and Bool (981)
  • Get the First and Last Word from a String or Sentence in SQL (861)
  • How to select distinct rows in a datatable in C# (856)

Recent Posts

  • Visual Studio: Fix Quick Actions Hiding Behind a Stale Squiggle
  • Photoshop: Fix a Smart Filter Resetting After You Relink It
  • Ajax: Fix a Cancelled Request Still Updating Stale UI State
  • C#: Fix a Span Captured by a Lambda Throwing at Runtime
  • The AI Prompt That Converts a REST Endpoint Into a Typed SDK

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com