🔍 See What Database Actually Does
Query slow? Don’t guess. Execution plans show exactly how database executes query. Find bottlenecks, fix performance.
Get Execution Plan
-- SQL Server SET STATISTICS IO ON; SET STATISTICS TIME ON; -- Show estimated plan (doesn't run query) SET SHOWPLAN_TEXT ON; GO SELECT * FROM orders WHERE user_id = 123; GO SET SHOWPLAN_TEXT OFF; -- Show actual plan (runs query) SET STATISTICS PROFILE ON; SELECT * FROM orders WHERE user_id = 123; SET STATISTICS PROFILE OFF; -- Or use SSMS: Ctrl + M (actual plan) -- PostgreSQL EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123; -- MySQL EXPLAIN SELECT * FROM orders WHERE user_id = 123;
🚨 Red Flags to Look For
| Problem | What It Means | Fix |
|---|---|---|
| Table Scan | Reading entire table | Add index |
| Index Scan (large) | Reading whole index | Better WHERE clause |
| Nested Loops | Join with no index | Add index on join column |
| Sort (high cost) | ORDER BY without index | Index on ORDER BY column |
| Key Lookup | Extra data fetch | Covering index |
| Implicit Conversion | Data type mismatch | Match column types |
Real Example: Before & After
❌ Before (Slow)
SELECT * FROM orders o INNER JOIN users u ON o.user_id = u.id WHERE o.status = 'pending' ORDER BY o.created_at DESC; -- Execution Plan Shows: -- Table Scan on orders (500,000 rows) -- No index on status column -- Sort operation (expensive) -- Execution time: 8 seconds
✅ After (Fast)
-- Add index CREATE INDEX idx_orders_status_date ON orders(status, created_at DESC); -- Same query SELECT * FROM orders o INNER JOIN users u ON o.user_id = u.id WHERE o.status = 'pending' ORDER BY o.created_at DESC; -- Execution Plan Shows: -- Index Seek (2,000 rows - only pending orders) -- No sort needed (already sorted in index) -- Execution time: 0.05 seconds -- 160x faster!
Reading The Numbers
Execution Plan Output: |--Index Seek (Cost: 5%) Rows: 100 Estimated Rows: 95 |--Nested Loops (Cost: 30%) Rows: 10,000 Estimated Rows: 100 ← Problem! Estimate way off |--Table Scan (Cost: 65%) ← Most expensive operation Rows: 500,000 Key Metrics: - Cost %: Higher = slower (focus on > 20%) - Rows: Actual rows processed - Estimated Rows: Database guess (way off = update stats)
🎯 Quick Wins
- Table Scan → Index Seek: Add index on WHERE columns
- Sort → No Sort: Index on ORDER BY columns
- Nested Loops → Hash Match: Update statistics
- Key Lookup: Create covering index (include SELECT columns)
- Parameter Sniffing: Use
OPTION (RECOMPILE)
💡 Pro Tips
- Use actual plan: Estimated can be wrong
- Look at percentages: Focus on operations > 20% cost
- Check row counts: Estimated vs Actual mismatch? Update stats
- Read right to left: Execution flows from right (data source) to left (result)
- Save plans: Compare before/after optimization
“Query took 30 seconds. Execution plan showed table scan. Added one index. Query now takes 0.1 seconds. 300x faster. Execution plans are X-ray vision for databases.”
