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 Queries Randomly Slow Down

- 02.01.26 | 03.01.26 - ErcanOPAK

Same query, different speed.

Why
Parameter sniffing.

Fix
Use OPTION (RECOMPILE) selectively.


🐌 Same Query, Different Speed

Nothing changed. Except the parameters.

If a query sometimes runs in 10 ms and sometimes in 10 seconds,
you’re probably not looking at a missing index.

You’re looking at parameter sniffing.


🚨 The Core Problem

SQL Server creates an execution plan based on the first parameter values it sees.

That plan is then cached and reused — even if future parameters behave very differently.

✔ Great for performance
❌ Terrible for uneven data distribution


💥 A Real-World Example

SELECT *
FROM Orders
WHERE CustomerId = @CustomerId

First execution

@CustomerId = 1   -- returns 5 rows

SQL Server chooses:

  • Nested loops

  • Index seek

  • “Small result set” plan

Next execution

@CustomerId = 9999  -- returns 500,000 rows

❌ Same plan
❌ Massive scan
❌ Query crawls

Same query.
Same indexes.
Different speed.


❌ Why This Happens

  • Execution plan is compiled once

  • First parameter “sniffs” the plan

  • Cached plan reused blindly

  • Data skew breaks assumptions

SQL Server isn’t wrong — it’s over-confident.


🛠 The Fix: OPTION (RECOMPILE)

SELECT *
FROM Orders
WHERE CustomerId = @CustomerId
OPTION (RECOMPILE);

This forces SQL Server to:

  • Recompile the plan

  • Use current parameter values

  • Generate the optimal plan each time


⚠️ Use It Selectively

Recompiling is not free.

✔ Perfect for:

  • Highly variable parameters

  • Large data skew

  • Rare but critical queries

❌ Avoid on:

  • High-frequency queries

  • Simple lookups

  • OLTP hot paths


🧠 Alternative Strategies

  • Local variables (sometimes)

  • Dynamic SQL

  • OPTIMIZE FOR hint

  • Query splitting

But when in doubt:
Selective recompile beats unpredictable performance.


🧾 TL;DR

❌ Same query ≠ same speed
❌ Cached plan ≠ optimal plan

✅ Parameter sniffing is the culprit
✅ OPTION (RECOMPILE) fixes it
✅ Use it carefully


SQL Server didn’t slow down.
It just trusted the wrong first impression.

Related posts:

SQL Tip: NULL should not be compared directly

SQL Tip: "NULL" should not be compared directly

SQL: Advanced Join Techniques for Complex Queries

SQL: Advanced Join Techniques for Complex Queries

SQL Server: Fix a Stored Procedure's Table-Valued Parameter Silently Ignoring a Newly Added Column B...

SQL Server: Fix a Stored Procedure's Table-Valued Parameter Silently Ignoring a Newly Added Column B...

Views: 34

Post navigation

Middleware Order Breaks Security
SQL Index Exists but Not Used

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,055)
  • Get the User Name and Domain Name from an Email Address in SQL (981)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (609)
  • Average of all values in a column that are not zero in SQL (570)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (547)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

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

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

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