SQL Server Query Performance Impacted by Join Containment Assumption in New Cardinality Estimator

Table of Contents

This article explores a performance phenomenon that can manifest in SQL Server 2014 and later versions when queries are compiled using the New Cardinality Estimator (New CE). Understanding this behavior is crucial for database professionals seeking to optimize query execution plans. The issue specifically relates to how the New CE estimates the number of rows resulting from join and filter operations, potentially leading to suboptimal plan choices.

Symptoms¶

Users may observe a degradation in query performance under specific conditions within SQL Server environments utilizing database compatibility level 120 or higher. This often occurs when executing queries that involve multiple tables connected through join operations, particularly when these queries also include filter conditions (predicates) applied to the joined tables. The performance slowdown is typically characterized by increased query execution time, higher CPU usage, or unexpected resource consumption compared to the same queries run under the Legacy Cardinality Estimator (Legacy CE).

The most telling symptom is a significant difference in the execution plan generated by the Query Optimizer. The plan might show incorrect join types, inefficient join orders, or inappropriate parallel execution choices, all stemming from inaccurate estimations of intermediate result set sizes. Specifically, the estimated number of rows after a join operation might be vastly different from the actual number of rows processed during execution. This problem is often absent when the database compatibility level is set to 110 or lower, or when the query is forced to use the Legacy CE via trace flags or query hints.

Cause¶

The fundamental reason for this performance difference lies in the evolution of the Query Optimizer’s cardinality estimation models. With the introduction of SQL Server 2014 and database compatibility level 120, Microsoft rolled out the New Cardinality Estimator. This updated model revised several assumptions previously used by the Legacy CE (used in compatibility levels 110 and below) when calculating the estimated number of rows for various query plan operators, such as joins and filters.

One significant change in the New CE is its approach to the “join containment assumption.” The Legacy CE operated under an assumption known as “Simple Containment.” This model essentially assumed a strong correlation between join predicates and filter predicates applied to the joined tables. It presumed that data being queried actually exists in both tables involved in an equijoin, implying that filter conditions on the joined columns would somehow be “contained” within the result set defined by the join itself. For instance, if a query joined Orders and Customers on CustomerID and filtered by CustomerID = 100, the Legacy CE might assume the filter predicate (CustomerID = 100) is fully correlated with the join predicate (Orders.CustomerID = Customers.CustomerID), leading to a potentially underestimated number of rows if the filter also significantly reduces the rows before or after the join in a way the model didn’t anticipate.

In contrast, the New CE employs “Base Containment.” This model adopts a more probabilistic and independent view of predicates. It assumes that filter predicates on separate tables are less likely to be highly correlated with join predicates unless explicitly defined otherwise. The New CE calculates the selectivity (the fraction of rows that satisfy a condition) of filters and joins more independently before combining them. While this approach is often more accurate for a wider range of queries and data distributions, in specific scenarios involving joins combined with non-join filter predicates, the assumption of independence can lead to an overestimation of the number of rows produced by the join operation. If the New CE significantly overestimates the join result because it didn’t assume correlation with filtering conditions that will be applied, the Query Optimizer might make poor decisions, such as choosing a Nested Loops join (efficient for small inner inputs) for what turns out to be a very large intermediate result set, or selecting an inefficient table access method.

The consequence of such inaccuracies in cardinality estimation is that the Query Optimizer, acting on flawed information about the size of intermediate results, may select an execution plan that is far from optimal for the actual data flow. This miscalculation in the planning phase directly translates into slower execution times and increased resource consumption during the execution phase. The impact is particularly pronounced when the join containment assumption plays a critical role in estimating the size of the intermediate result set that feeds into subsequent operators in the query plan.

Resolution¶

Fortunately, SQL Server provides mechanisms to address this specific issue without necessarily reverting the entire database to the Legacy CE model, which might negatively impact other queries. The primary solutions involve guiding the Query Optimizer to use the Simple Containment assumption for problematic queries.

For SQL Server 2014 and later versions, trace flag 9476 can be enabled. This trace flag forces the Query Optimizer, when using the New CE, to adopt the Simple Containment assumption for all queries compiled within its scope (global, session, or query level). Enabling this trace flag globally (DBCC TRACEON(9476, -1) WITH NO_INFOMSGS) will affect all queries on the server, which might be too broad. A more targeted approach is to enable it per session (DBCC TRACEON(9476) WITH NO_INFOMSGS) or, ideally, per specific query using the OPTION (QUERYTRACEON 9476) hint.

-- Example using trace flag 9476 with OPTION hint
SELECT *
FROM Table1 t1
JOIN Table2 t2 ON t1.Col1 = t2.Col1
WHERE t1.Col2 = 100
OPTION (QUERYTRACEON 9476);

A more modern and often preferred method, available since SQL Server 2016 SP1, is to use the USE HINT query hint. Specifically, the hint ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS explicitly instructs the Query Optimizer to use the Simple Containment assumption for the query it is applied to. This is a more granular approach than using a trace flag, allowing you to fine-tune behavior on a per-query basis. Using query hints is generally recommended over trace flags when a hint exists for the specific behavior you want to modify, as hints are part of the SQL language and are often easier to manage and understand in the context of the query itself.

-- Example using USE HINT for Simple Containment
SELECT *
FROM TableA a
JOIN TableB b ON a.ID = b.ID
WHERE a.Status = 'Active' AND b.Type = 'Premium'
OPTION (USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'));

Implementing either trace flag 9476 or the ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS hint is beneficial when you’ve diagnosed a performance issue directly linked to an inaccurate join cardinality estimation under the New CE. This is particularly relevant when:

1. Queries with joins and non-join filters perform significantly worse under New CE compared to Legacy CE.
2. Analyzing the execution plan shows a substantial discrepancy between the estimated number of rows after a join operation and the actual number of rows processed.
3. Applying the trace flag or the hint resolves the performance issue by causing the Optimizer to choose a demonstrably better execution plan.

It’s crucial to test the impact of these changes thoroughly in a non-production environment before deploying them. While forcing Simple Containment can fix specific problems, it might potentially negatively affect other queries that rely on the Base Containment assumption for accurate estimation. Therefore, per-query application using the USE HINT is often the safest and most precise method.

To diagnose this issue effectively, compare the execution plans (estimated and actual) for the problematic query:
* Run the query normally with the New CE enabled.
* Run the query with OPTION (QUERYTRACEON 9476) or OPTION (USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS')).
* Compare the estimated and actual row counts for relevant operators, especially joins, and observe the overall structure of the execution plan. Look for changes in join types (e.g., switching from Nested Loops to Hash Match) or changes in the join order. The plan that performs better in testing is the one to favor. Tools like SQL Server Management Studio’s graphical execution plan viewer or analyzing the XML plan output can be invaluable for this comparison.

Understanding the nuances of the Query Optimizer’s cardinality estimation, particularly the shift in join containment assumptions between Legacy and New CE, empowers database administrators and developers to diagnose and resolve performance bottlenecks in modern SQL Server versions. While the New CE is designed to be more accurate overall, specific data patterns and query structures can sometimes benefit from leveraging the older assumption model for improved plan quality and performance.

Troubleshooting involves verifying the root cause through execution plan analysis. If the estimated rows after a join are wildly inaccurate (especially an overestimation) and this inaccuracy disappears when forcing Simple Containment, you’ve likely found the source of the problem. Implementing the hint or trace flag provides a targeted solution to guide the optimizer towards a more efficient path based on a different, potentially more appropriate, assumption for your specific data and query pattern.

Beyond addressing the join containment assumption, always ensure that database statistics are up-to-date and relevant, and that appropriate indexes are in place. While CE issues can cause significant problems, sometimes the underlying data structures or statistics are the primary cause of poor query performance. However, when statistics and indexing are sound, and the performance problem correlates specifically with the upgrade to New CE, investigating the join containment assumption is a vital step. The Query Store, introduced in SQL Server 2016, is an excellent feature to track query performance and execution plans over time, making it easier to identify plan regressions linked to CE changes or other factors.

Ultimately, resolving performance issues related to cardinality estimation often requires a careful analysis of the execution plan and understanding the assumptions the Optimizer is making. The tools and hints provided by SQL Server allow for fine-grained control to override default behaviors when they lead to suboptimal outcomes, ensuring your critical queries run efficiently.

Do you have queries that experienced performance degradation after migrating to SQL Server 2014 or later? Have you investigated cardinality estimation or used query hints like ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS? Share your experiences and troubleshooting tips in the comments below!