SQL Server › Query Optimization & Plans

What are the most common causes of poor query performance that you should check first when troubleshooting a slow query?

Intermediate

Answer

Common causes include missing or unused indexes on columns used in WHERE clauses and joins, outdated statistics leading to poor row estimates, functions applied directly to columns in a WHERE clause which prevent index usage, implicit data type conversions caused by mismatched column types, and queries that return far more columns or rows than are actually needed by the application.

-- A function on the column prevents index usage
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2026;

-- Rewritten to allow index usage
SELECT * FROM Orders WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01';

Real-world example A developer rewrites a slow query that was applying a function directly to a date column, allowing SQL Server to use an existing index on that column and dramatically improving the query's performance.

Common follow-ups: Why does applying a function to a column in a WHERE clause prevent index usage?;What tools help quickly identify these common performance mistakes in existing queries?

Indexes;Data Types & Schema Design