Answer
Poor execution plans are usually symptoms of deeper issues in schema design, statistics, or query patterns.
Common causes:
- Missing or inappropriate indexes: Forcing table scans or expensive lookups.
- Stale or missing statistics: Leading to incorrect row estimates and wrong join strategies.
- Parameter sniffing: Plan optimized for one parameter value, reused for others with different data distributions.
- Scalar functions and multi-statement TVFs: Preventing optimization and parallelism.
- Complex views over views: Obscure actual data access and create bloated plans.
- Implicit conversions: Causing non-SARGable predicates or index misses.
- RBAR patterns (cursors, loops): Neglecting set-based approaches.
Effective tuning often involves query simplification, better indexing, and statistics maintenance rather than just tweaking server settings.