In real projects, performance issues don’t come with clear errors.
They appear as:
- Slow application
- Timeouts
- Random failures
In this blog, we’ll walk through a real-world style incident and solve it step by step using Microsoft SQL Server.
1️⃣ The Problem
📌 Scenario
A live production system suddenly became slow:
- API response time: 200 ms → 15 seconds
- Users complaining
- CPU usage high
No code deployment was done recently.
2️⃣ Initial Investigation
First rule: Don’t panic. Gather data.
Check running queries:
SELECT *
FROM sys.dm_exec_requests
WHERE status = 'running';
Observation:
✔ One stored procedure consuming high CPU
✔ Running multiple times
3️⃣ Identify the Problem Query
Problem stored procedure:
EXEC dbo.GetUserOrders @UserId = 100;
4️⃣ Check Execution Plan
Enabled actual execution plan.
Findings:
❌ Table scan on Orders (10 million rows)
❌ High cost operator
❌ Missing index warning
5️⃣ Check Indexes
Query:
SELECT *
FROM Orders
WHERE UserId = @UserId;
Issue:
❌ No index on UserId
6️⃣ Immediate Fix
Created index:
CREATE INDEX IX_Orders_UserId
ON Orders(UserId);
7️⃣ Result After Fix
| Before | After |
|---|---|
| 15 sec | 120 ms |
Huge improvement 🎯
8️⃣ But Problem Came Back Again…
After a few hours:
❌ Query slow again
❌ Same procedure
9️⃣ Deep Investigation
Checked execution plan again.
New Issue:
❌ Parameter sniffing
What Happened?
- First execution used small dataset → good plan
- Next execution used large dataset → bad plan reused
🔟 Fix Parameter Sniffing
Solution 1: Use OPTION (RECOMPILE)
SELECT *
FROM Orders
WHERE UserId = @UserId
OPTION (RECOMPILE);
Solution 2: Use Local Variable
DECLARE @LocalUserId INT = @UserId;SELECT *
FROM Orders
WHERE UserId = @LocalUserId;
1️⃣1️⃣ Additional Findings
🔍 Blocking Issue
EXEC sp_who2;
Found:
✔ Blocking caused by long transaction
Fix
✔ Reduced transaction time
✔ Optimized update query
1️⃣2️⃣ Final System State
After all fixes:
✔ Query execution stable
✔ Response time: ~100 ms
✔ CPU usage reduced
✔ No blocking
1️⃣3️⃣ Lessons Learned
✔ Always check execution plan first
✔ Indexing is critical
✔ Parameter sniffing is real
✔ Monitoring is essential
✔ Fix root cause, not symptoms
1️⃣4️⃣ Production Debugging Checklist
When facing performance issue:
✔ Identify slow query
✔ Check execution plan
✔ Verify indexes
✔ Check statistics
✔ Look for blocking
✔ Analyze parameter sniffing
✔ Apply fix and monitor
1️⃣5️⃣ Pro Tips
✔ Never change multiple things at once
✔ Always test fixes
✔ Monitor after deployment
✔ Document root cause
✍️ Conclusion
Real-world SQL debugging is about:
✔ Systematic approach
✔ Understanding internals
✔ Using the right tools
This case study shows how small issues like:
- Missing index
- Parameter sniffing
can cause major production failures.
🔥 This blog is a gold-level content piece — perfect for interviews and real-world learning.