Adaptive Cardinality Estimation
Adaptive cardinality estimation corrects the optimizer row-count guesses at runtime by observing actual output sizes, so that a plan chosen on stale statistics can re-optimize mid-execution when.
Definition
Adaptive cardinality estimation is the optimizer technique where the initial plan is based on statistics, but during execution the actual row counts are observed and, if they diverge past a threshold, the remaining plan is re-optimized with the real cardinality, avoiding the catastrophic plan that a bad estimate produces.
Context and scope
Applied in modern query engines (SQL Server adaptive joins, Snowflake), where statistics drift and a single bad estimate can turn a 10ms query into a 10s scan.
Common misconceptions
- Good statistics make adaptive estimation unnecessary — statistics drift, data skews, and parameter sniffing produce bad estimates even with fresh statistics
- Adaptive plans are just plan hints — hints fix one plan; adaptive estimation re-optimizes based on observed reality, generalizing across queries
Anti-patterns
- Disabling adaptive estimation because it adds per-query overhead, then suffering the bad-estimate plan on every skewed query
- Re-optimizing so frequently that the optimization cost exceeds the execution cost, making the feature a net loss
Related concepts
Authoritative source
https://learn.microsoft.com/en-us/sql/relational-databases/performance/adaptive-query-processing
What it is
Adaptive cardinality estimation is the optimizer technique where the initial plan is based on statistics, but during execution the actual row counts are observed and, if they diverge past a threshold, the remaining plan is re-optimized with the real cardinality, avoiding the catastrophic plan that a bad estimate produces. Applied in modern query engines (SQL Server adaptive joins, Snowflake), where statistics drift and a single bad estimate can turn a 10ms query into a 10s scan. The data model and the access patterns are co-designed: a schema that is correct in the relational sense can be catastrophic for the queries the workload actually issues.
How it works
Indexing decisions follow the query shapes, not the column importance: a covering index turns a table scan into an index-only scan, but every index adds write amplification and storage cost. Partitioning aligns with the access dimension so a query touches one partition, not all. Transaction scope is minimized to hold locks briefly; long transactions are the root of contention and deadlock.
When to apply and pitfalls
Apply when data volume or query latency has a concrete business cost. The common pitfall is indexing for the current workload and ignoring the access patterns that growth will introduce — the index that helps today can serialize writes tomorrow. Another is treating the database as a black box, missing that the optimizer’s plan choice depends on statistics that drift as data changes.