The stored procedure ran fine in SSMS — under 1 second, every time. But through the .NET API? 30–120 seconds. Timeouts. Errors.

A production system started timing out.

The stored procedure ran fine in SSMS — under 1 second, every time.

But through the .NET API? 30–120 seconds. Timeouts. Errors.

No CPU spikes. No blocking. No infrastructure alerts.

The DBA checked. The infrastructure team checked. Nothing.

So what changed?

━━━ The investigation ━━━

The timeline had one clue: the degradation started right after routine DBA maintenance — a statistics update and index rebuild.

That narrowed it down. The maintenance invalidated the execution plan cache. SQL Server was forced to recompile the procedure on the next call.

But why was SSMS fast and the API slow — on the exact same query?

The answer was hiding in a DMV:

↳ sys.dm_exec_cached_plans showed TWO cached plans for the same stored procedure.

↳ Same query. Same parameters. Two completely different execution strategies.

One had ~50 logical reads. The other had ~85,000.

━━━ The root cause ━━━

SQL Server uses a bitmask of SET options as part of the plan cache key.

SSMS connects with ARITHABORT ON.

Entity Framework / ADO.NET connects with ARITHABORT OFF.

That single bit difference meant SQL Server couldn't reuse the SSMS plan for the application session. It compiled a fresh plan — and under the new post-maintenance statistics, that fresh plan chose a full index scan instead of an index seek.

The result: 85,000 logical reads instead of 50. Every. Single. Call.

━━━ The proof ━━━

Running this against sys.dm_exec_query_stats confirmed it:

→ set_options: 4345 (SSMS) → avg logical reads: 50

→ set_options: 4281 (EF) → avg logical reads: 85,420

The difference between 4345 and 4281? Bit 6. Value 64. ARITHABORT.

━━━ The fix ━━━

Immediate: targeted DBCC FREEPROCCACHE on the procedure's plan handle — not a server-wide flush.

Durable: SET ARITHABORT ON in the EF DbContext initialization, so application sessions align with SSMS behavior and share the same plan cache slot.

━━━ The lessons ━━━

→ Fast in SSMS ≠ fast in production. SSMS uses different SET options than your application. Always validate performance under application context.

→ DBA maintenance doesn't cause performance issues — it reveals them. The regression was always latent; the plan cache invalidation just exposed it.

→ The same stored procedure can have multiple cached plans with radically different performance. When SSMS is fast and your app is slow, check set_options in the plan cache first.

→ Queries with TOP(1) + ORDER BY + non-covering indexes are especially sensitive to plan regression. Small changes in statistics can flip the optimizer between a seek and a scan.

One bit. 85,370 extra logical reads. 10 hours of production degradation.

SQL Server is not a black box — the evidence is always in the DMVs.