Understanding USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION') in Azure SQL DB
Query performance tuning is one of the most critical aspects of maintaining stable and predictable workloads in Azure SQL Database. While Microsoft continuously improves the SQL Server Cardinality Estimator (CE) to provide more accurate row estimates and better plan choices, there are scenarios where workloads may benefit from reverting to the Legacy CE—especially when upgrading databases or encountering regressions after plan compilation. One practical and targeted way to influence the optimizer’s behavior is the query‑level hint: OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')); In this blog, we explore what this hint does, when to use it, and how it impacts execution plans in Azure SQL Database. 1. What Is the Cardinality Estimator? The Cardinality Estimator predicts the number of rows (cardinality) processed at each step of an execution plan. These estimates heavily influence the optimizer’s choices around: Join algorithms (Nested Loop, Hash, Merge) Index selection Memory grants Parallelism decisions Operator placement and costing The New CE (introduced in SQL Server 2014+) typically handles modern workloads better through richer correlation assumptions and updated statistical heuristics. However, the Legacy CE can outperform the New CE for certain workloads—particularly those with: Highly skewed data distributions Complex predicates Uncorrelated filter predicates Legacy application patterns relying on outdated parameter sniffing assumptions 2. What Does FORCE_LEGACY_CARDINALITY_ESTIMATION Do? The hint forces the optimizer to compile the query using the Legacy CE, regardless of: Database compatibility level Global database settings (ALTER DATABASE SCOPED CONFIGURATION) Query Store settings (unless a forced plan exists) It provides a granular, non-intrusive, and risk‑controlled way to compare the two estimators side-by-side. Where the hint helps Queries suffering regressions after upgrading compatibility levels Workloads where the New CE underestimates/overestimates row counts Parameter‑sensitive queries sensitive to distribution skew Scenarios where Query Store shows the Legacy CE plan as consistently faster 3. How It Impacts Execution Plans Applying USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION') affects the optimizer in several ways: A. Row Estimation Logic Legacy CE assumes more independence between predicates. New CE introduces more correlation assumptions. Effect: Legacy CE may estimate higher cardinality for multi‑predicate filters Higher estimates → optimizer may choose Hash Join or Merge Join over Nested Loops Lower estimates → may reduce memory grants or favor Nested Loops B. Join Strategy Changes Example difference: Legacy CE: Hash Join due to higher row estimates New CE: Nested Loop due to underestimated rows This can dramatically alter performance depending on input sizes. C. Memory Grants Legacy CE often yields larger memory grants due to conservative estimates. This can be positive or negative: Positive: Avoids spills to tempdb Negative: Excessive memory grants can lead to concurrency pressure D. Parallelism Decisions Legacy CE may push optimizer toward parallel plans when it predicts larger input sizes. E. Index and Predicate Choices Legacy CE may select different indexes because estimated filter selectivity differs. This can impact: Index seek vs. index scan decisions Predicate evaluation order Operator costing 4. Real‑World Example Here’s a simplified example comparing the same query compiled via: Default CE Legacy CE via hint SELECT c.CustomerId, o.OrderId, o.TotalAmount FROM dbo.Customers c JOIN dbo.Orders o ON o.CustomerId = c.CustomerId WHERE c.Country = 'US' AND o.OrderDate > DATEADD(day, -30, GETUTCDATE()) OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')); Observations typically seen: Default CE → Underestimates rows on Country = 'US' due to skew → Nested Loop Legacy CE → Higher estimate → Hash Join performing better for large inputs 5. When Should You Use This Hint? Recommended scenarios You observe a regression after increasing database compatibility level Query Store indicates the Legacy CE plans consistently outperform Estimation errors lead to tempdb spills or poor join choices You want to validate behavior before applying database‑wide changes Avoid using it when You haven’t validated the effect using Query Store or actual runtime statistics Only small regressions occur and can be solved by updating statistics The root cause is a missing index, outdated statistics, or a data distribution issue 6. Alternatives to the Query-Level Hint Before locking the query to Legacy CE, consider the broader options: A. Query Store Plan Forcing If a Legacy CE plan performs better, Query Store allows you to force it without hints. B. Database Scoped Configuration You can change the database CE behavior globally: ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON; But be careful—this affects all queries. C. Compatibility Level Change Some workloads stabilize when pinned to a previous compatibility level, but this is not preferred long term. D. Fixing Statistics and Indexing Always validate: Up‑to‑date statistics Correct indexing strategy No parameter‑sniffing issues 7. Best Practices for Using FORCE_LEGACY_CARDINALITY_ESTIMATION Test first in lower environments Confirm impact using Query Store (sys.query_store_plan, sys.query_store_runtime_stats) Compare CPU, reads, duration, spills, memory grants Monitor for regressions after index changes or statistics updates Document all queries using hints for long‑term maintainability 8. Conclusion USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION') is a powerful and precise tool for controlling query behavior in Azure SQL Database. Although the New CE is generally superior, certain workloads still perform better under the Legacy CE due to its more conservative and less correlated estimation strategies. This hint allows you to: Mitigate regressions Improve plan stability Avoid global configuration changes Make evidence-backed decisions using Query Store
