SQL SERVER, Your Execution Plan Is Lying to You!

😈 Your Execution Plan Is Lying to You ...And You Don’t Know It

Hi SQL Server guys,

Follow me to explore another performance issue. I’m sure this will happen to you at some point ...and to many of your colleagues as well.

A customer tells you that your application is dramatically slow. You’ve already identified the problematic query and…

Execution plan looks perfect…
but your query is still slow.

👉 Something is lying to you.

And no… it's not SQL Server! ....it's the way you're reading the execution plan.

---

👉 Previous Post

If you missed the previous deep dive you can take a look to:
SQL Server 2025 CU3 – Backup/Restore Performance Benchmark

---

⚠️ The Core Problem: Estimated vs Actual Rows

Most developers look at execution plans and think:

• ✔️ Index Seek → good
• ✔️ Low cost → good
• ✔️ No warnings → perfect

👉 “Looks perfect… ship it.”

But the real problem is here:

Estimated Rows ≠ Actual Rows

---

🧪 Example 1 – The Classic Trap

SELECT *
FROM Sales
WHERE CustomerId = @CustomerId;

Execution plan says:

• Estimated rows: 1
• Actual rows: 250,000

💥 Result:

• Nested Loop chosen instead of Hash Join
• Repeated lookups
• CPU spike
• Query becomes slow

---

📊 Benchmark 1 – Good vs Bad Estimation

Scenario	Estimated Rows	Actual Rows	Logical Reads	CPU	Duration
Good estimation	1000	1200	5,000	50 ms	80 ms
Bad estimation	1	250,000	1,200,000	3,500 ms	8,200 ms

👉 Same query. Same index. Completely different performance.

---

🧠 Why This Happens (Cardinality Estimation)

SQL Server uses statistics to estimate how many rows a query will return.

But estimation can fail due to:

• Outdated statistics
• Skewed data distribution
• Parameter sniffing
• Complex predicates

👉 SQL Server is guessing… and sometimes guessing VERY wrong.

---

🧪 Example 2 – Parameter Sniffing

CREATE PROCEDURE GetOrders
    @CustomerId INT
AS
SELECT *
FROM Orders
WHERE CustomerId = @CustomerId;

First execution:

• @CustomerId = 1 → 1 row

Second execution:

• @CustomerId = 500 → 300,000 rows

💥 Same plan reused → disaster.

---

📊 Benchmark 2 – Parameter Sniffing Impact

Execution	Rows	Plan Type	Duration
First (small)	1	Index Seek + Nested Loop	5 ms
Second (large)	300,000	👉Same plan reused	6,500 ms

---

👻 Hidden Problems You Don’t See

Execution plans don’t always show real bottlenecks clearly:

• TempDB spills
• Memory grant issues
• Implicit conversions
• Parallelism skew

💡 Plan looks clean… runtime is not.

---

🔍 How to Detect the Lie

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Your query here

And always check:

• Actual vs Estimated rows
• Logical reads
• CPU time
• Wait stats

---

🚀 How to Fix It

• UPDATE STATISTICS
• Use OPTION (RECOMPILE)
• Optimize indexes
• Rewrite predicates
• Use proper data types

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

---

🎯 Final Thought

Execution plans don’t lie.

You just don’t know where to look.

👉 Stop trusting the plan blindly. Start reading reality.

Biondi Luca @2026