💥 The Database Meltdown
9 AM Monday. Database CPU: 98%. Queries timing out. Users can’t log in. Boss is screaming. You have 1 hour to fix it before the CEO finds out.
The 5 Performance Killers (And How to Fix Them)
💣 Killer #1: SELECT *
❌ SLOW (transfers 50 columns, 10MB) SELECT * FROM Users WHERE Status = 'Active'; ✅ FAST (transfers 2 columns, 100KB) SELECT UserId, Name FROM Users WHERE Status = 'Active'; Result: 100x less data transferred Network time: 5s → 0.05s
💣 Killer #2: N+1 Query Problem
❌ SLOW (1001 queries!)
-- Get all orders
SELECT * FROM Orders;
-- For each order, get customer (in application code loop)
SELECT * FROM Customers WHERE CustomerId = 123; -- x1000
Total: 1001 queries, 10 seconds
✅ FAST (1 query with JOIN)
SELECT
o.*,
c.Name,
c.Email
FROM Orders o
INNER JOIN Customers c ON o.CustomerId = c.CustomerId;
Total: 1 query, 0.1 seconds = 100x faster
💣 Killer #3: Missing Indexes
❌ SLOW (full table scan - reads 1M rows) SELECT * FROM Orders WHERE CustomerId = 12345 AND Status = 'Shipped'; Execution plan: Table Scan Time: 8 seconds ✅ FAST (index seek - reads 10 rows) -- Add composite index CREATE INDEX IX_Orders_Customer_Status ON Orders(CustomerId, Status); Execution plan: Index Seek Time: 0.02 seconds = 400x faster!
💡 Index Order Matters!
Index on (CustomerId, Status) is FAST for this query.
Index on (Status, CustomerId) would be SLOW (wrong column order).
Rule: Most selective column first (CustomerId more unique than Status)
💣 Killer #4: Functions on Indexed Columns
❌ SLOW (can't use index - applies UPPER to every row) SELECT * FROM Users WHERE UPPER(Email) = 'USER@EXAMPLE.COM'; Index on Email is useless! ✅ FAST (uses index) -- Store email lowercase, search lowercase SELECT * FROM Users WHERE Email = 'user@example.com'; -- Or use computed column with index ALTER TABLE Users ADD EmailLower AS LOWER(Email) PERSISTED; CREATE INDEX IX_Users_EmailLower ON Users(EmailLower); SELECT * FROM Users WHERE EmailLower = 'user@example.com';
💣 Killer #5: OR Conditions
❌ SLOW (can't efficiently use indexes) SELECT * FROM Products WHERE CategoryId = 5 OR Price > 1000; Database must scan entire table ✅ FAST (use UNION - separate index seeks) SELECT * FROM Products WHERE CategoryId = 5 UNION SELECT * FROM Products WHERE Price > 1000; Each query uses its own index efficiently!
| Problem | Before Fix | After Fix | Improvement |
|---|---|---|---|
| SELECT * | 5 seconds | 0.05s | 100x |
| N+1 Queries | 10 seconds | 0.1s | 100x |
| Missing Index | 8 seconds | 0.02s | 400x |
| Function on Column | 6 seconds | 0.05s | 120x |
| OR Conditions | 4 seconds | 0.1s | 40x |
🔍 How to Find Slow Queries
-- SQL Server: Find top 10 slowest queries
SELECT TOP 10
total_elapsed_time / execution_count / 1000 AS avg_ms,
execution_count,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;
-- Check if query is using indexes (Execution Plan)
SET STATISTICS IO ON;
SELECT * FROM Orders WHERE CustomerId = 123;
-- Look for "Table Scan" (bad) vs "Index Seek" (good)
💡 Quick Wins Checklist
- ✓ Index foreign keys (90% of JOIN columns need indexes)
- ✓ Index WHERE clause columns (especially in frequent queries)
- ✓ Use EXPLAIN/Execution Plan on every slow query
- ✓ Monitor database metrics (CPU, disk I/O, wait stats)
- ✓ Set query timeout (kill runaway queries)
- ✓ Enable slow query log (catch problems early)
“Our database was dying. Added 3 indexes, rewrote 5 queries. Database CPU dropped from 95% to 20%. Crisis averted in 45 minutes. These 5 tricks literally saved our jobs.”
⚠️ Don’t Over-Index!
Every index speeds up reads but slows down writes. Too many indexes:
- Slows INSERT/UPDATE/DELETE (each must update all indexes)
- Uses disk space (each index = copy of columns)
- Confuses query optimizer (too many choices)
Rule of thumb: 3-5 indexes per table max. Index ONLY frequently-queried columns.