Strategic Use of Legacy Cardinality Estimation in Azure SQL DB

Azure SQL Database continuously evolves its Cardinality Estimator (CE) to improve query optimization, but recent updates reveal scenarios where reverting to legacy behavior provides tangible performance benefits. The OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')) hint offers a surgical solution for enterprises facing query regressions after compatibility level upgrades or statistical shifts.

What Changed: The Cardinality Estimator Divide

Microsoft introduced a redesigned Cardinality Estimator in SQL Server 2014 (carried into Azure SQL DB) to handle modern workloads with complex correlations. This New CE typically outperforms the Legacy CE for most scenarios but creates regressions in specific cases:

• Queries with highly skewed data distributions
• Applications relying on pre-2014 parameter-sniffing assumptions
• Workloads with uncorrelated filter predicates

The New CE's more sophisticated correlation modeling sometimes misestimates row counts when predicates aren't statistically independent. When Azure SQL DB databases upgrade compatibility levels (e.g., from 110 to 150), these miscalculations can trigger inefficient execution plans.

Provider Comparison: Legacy vs. New CE Behavior

Behavior	Legacy CE	New CE
Predicate Independence	Assumes full independence	Models correlations between predicates
Join Strategy	Favors Hash/Merge Joins (higher row estimates)	May choose Nested Loops (lower estimates)
Memory Grants	Larger, preventing spills but risking contention	Tighter, risking tempdb spills
Parallelism	More likely with large input predictions	More conservative parallelism

This divergence manifests concretely: A query filtering orders by Country='US' and OrderDate might see the New CE underestimate rows due to data skew, choosing a slow Nested Loop join. The Legacy CE's higher estimate could correctly trigger a Hash Join. Azure documentation details hint implementation.

Business Impact: Strategic Performance Control

The hint's granularity provides three key advantages:

1. Regression Mitigation: Resolve performance drops after compatibility upgrades without rolling back entire databases
2. Plan Stability: Lock efficient Legacy CE plans via Query Store forcing instead of database-wide changes
3. Resource Optimization: Prevent excessive memory grants (Legacy) or tempdb spills (New CE) based on workload patterns

However, indiscriminate use risks technical debt. Enterprises should validate through:

• Query Store runtime stats (CPU, reads, duration)
• A/B testing in non-production environments
• Index/statistics analysis before defaulting to legacy behavior

Strategic Alternatives and Best Practices

Before applying the hint, consider:

• Database-scoped configuration: ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION=ON affects all queries—use cautiously
• Statistics updates: Refresh outdated stats with UPDATE STATISTICS
• Index tuning: Add filtered indexes for skewed predicates

When using the hint:

1. Document all hinted queries for future audits
2. Monitor performance after index/statistics changes
3. Prefer Query Store plan forcing over hints when possible

This hint exemplifies Azure SQL DB's flexibility: Enterprises can adopt modern features while temporarily preserving legacy behavior where proven beneficial. For teams managing critical workloads, it’s a precision tool for balancing innovation and stability.