Today it’s fine. Tomorrow it’s slow. Why Schema changes Wider rows More IO Fix Select only what you need. SELECT Id, Name FROM Users;
Category: SQL
Indexes Can Make Queries Slower (Here’s Why)
Too many indexes = slower writes. Why Every INSERT/UPDATE must update all indexes. Rule Indexes are not free. Audit sys.dm_db_index_usage_stats
Why SELECT * Slowly Destroys Performance
It’s not about bandwidth — it’s about execution plans. Problems Wider rows = more IO Breaks covering indexes Schema changes silently hurt queries Fix Always project explicitly: SELECT Id, Name, CreatedAt FROM Users;
Avoid SELECT * in Production
Why it mattersSchema changes won’t silently break performance.
Covering Indexes Reduce Lookups
INCLUDE (Name, Email) Why it mattersQueries hit index only → faster reads.
Use EXISTS Instead of COUNT(*)
Why it mattersStops scanning once a match is found.
Partial Indexes Save Space
CREATE INDEX idx_active_users ON Users(Id) WHERE IsActive = 1; Why it mattersIndex only what you query.
Avoid SELECT *
Why it mattersBreaks caching, increases IO, hides schema drift.
Covering Indexes Prevent Lookups
CREATE INDEX IX_User_Email ON Users (Email) INCLUDE (Name); Why it mattersFewer disk reads = faster queries.
Use EXISTS Instead of IN for Better Query Plans
SELECT * FROM Users u WHERE EXISTS ( SELECT 1 FROM Orders o WHERE o.UserId = u.Id ); Why this mattersEXISTS short-circuits; IN often materializes full sets.
Why COUNT(*) Can Be Slow on Large Tables
Counting rows forces full scans. SELECT COUNT_BIG(*) FROM Orders WITH (NOLOCK); Why this mattersOn massive tables, counts are expensive — cache or approximate when possible.
The Hidden Cost of OFFSET Pagination
Large offsets force the database to scan and discard rows. SELECT * FROM Orders WHERE Id > @LastSeenId ORDER BY Id FETCH NEXT 20 ROWS ONLY; Why this worksKeyset pagination scales infinitely.
Why SELECT * Slowly Destroys Performance
Indexes don’t help when you ask for everything. SELECT Id, Name FROM Users WHERE IsActive = 1; Why this mattersSmaller payload, better index usage, faster IO.
Why EXISTS Beats COUNT(*) in Conditional Queries
You don’t need numbers when you only need truth. IF EXISTS ( SELECT 1 FROM Orders WHERE CustomerId = 42 ) BEGIN PRINT ‘Has orders’ END Why it’s faster Stops at the first match Avoids full scans Reduces CPU usage
Why SELECT * Is a Silent Performance Killer (Even With Indexes)
Databases return exactly what you ask for.SELECT * forces unnecessary IO, memory usage, and network transfer. SELECT OrderId, OrderDate, TotalAmount FROM Orders WHERE CustomerId = 42; Cause → Effect Extra columns → wider rows → slower queries Explicit columns → faster reads + stable schemas Bonus: Schema changes won’t break consumers.
SQL COUNT(*) Slows Down Reports
Simple count, slow execution. Why it happensFull table scan. Why it mattersReports lag. Vital fix Use indexed counts when possible.
SQL Index Exists but Is Ignored
Planner avoids it. Why it happensLow selectivity. Why it mattersSlow queries. Vital fix Index columns with high cardinality.
SQL Date Comparisons Kill Indexes
Indexes exist, unused. Why it happensFunctions on columns. Why it mattersFull table scans. Smart fixCompare ranges instead. WHERE CreatedAt >= ‘2024-01-01’ AND CreatedAt < ‘2024-02-01’
SQL Queries Slow After Index Added
Index made it worse. Why it happensWrong column order. Why it mattersOptimizer chooses bad plans. Smart fixIndex on filter columns first.
SQL NULL Handling Breaks Logic
Counts don’t match reality. Why it happensNULL is not zero. Why it mattersBusiness logic errors. Smart fixAlways handle NULL explicitly. COALESCE(value, 0)
SQL Queries Slow Down Over Time
Same query, worse performance. Why it happensStatistics become outdated. Why it mattersQuery plans degrade. Smart fixUpdate statistics regularly.
SQL Deletes Lock Tables
Simple delete, big impact. WhyLarge batch deletes. TipDelete in chunks. DELETE TOP (1000) FROM Logs;
SQL Queries Slow Despite Indexes
Indexes exist, still slow. WhyFunctions used on indexed columns. TipAvoid wrapping indexed columns. WHERE CreatedDate >= ‘2025-01-01’
SQL Deadlocks Appear Sporadically
Hard to reproduce. WhyInconsistent access order. TipStandardize table access order.
SQL Queries Break After Schema Changes
Query logic unchanged. WhyImplicit column order dependency. TipAlways specify column names.
SQL Queries Return Correct Data Too Slowly
Logic fine, execution heavy. WhyMissing covering indexes. TipInclude frequently selected columns.
SQL Performance Drops After Adding “Helpful” Indexes
Index count rises, speed drops. WhyWrite amplification. TipIndex only what queries actually use.
SQL Performance Depends on Data Shape
Same query, different speed. WhyData distribution changes execution plans. TipReview execution plans regularly.
SQL Tables Age Worse Than Code
Schema works until it doesn’t. WhyNo room for evolution. TipDesign columns with future growth in mind.
SQL Queries Break at Scale
Worked fine in staging. WhyProduction data distribution differs. Tip Test with production-like data sizes.