Why SQL Server Optimizer May Eliminate Foreign Key Joins


Let’s look at an example of Foreign Key Join elimination. The following query joins two tables and shows the execution plan in Figure 5:


Figure 5 Original plan joining two tables


Let’s see what happens if we comment out the Account Number column:


If you run the query again, the Customer table and obviously the join operation are eliminated, as can be seen in the execution plan in Figure 6.


Figure 6 Foreign Key Join elimination example

There are two reasons for this change. First, because the Account Number column is no longer required, no columns are requested from the Customer table. However, it seems like the Customer table is still needed because it is required as part of the equality operation on a join condition. That is, SQL Server needs to make sure that a Customer record exists for each related record in the Individual table. Actually, this validation is performed by the existing foreign key constraint, so the query optimizer realizes that there is no need to use the Customer table at all. This is the defined foreign key (again, there is no need to run this code):


As a test, temporarily disable the foreign key by running the following statement:


Now run the previous query again. Without the foreign key constraint, SQL Server has no choice but to perform the join operation to make sure that the join condition is executed. As a result, it will use a plan joining both tables again, similar to the one shown previously in Figure 5. Don’t forget to re-enable the foreign key by running the following statement:


Finally, you can also see this behavior on the created logical trees. To see this again, use the undocumented trace flag 8606, as shown next:


You can see an output similar to this, edited to fit the page:




In the output, you can see that although the second tree was highly simplified (the input tree was edited to fit the page), both the input and the simplified tree still have logical Get operators, or LogOp_Get for the Sales.SalesOrderHeader and Sales.Customer tables. The join-collapsed tree has eliminated one of the tables, showing only Sales.SalesOrderHeader. Notice that the tree was simplified after the original input tree and, after that, the join was eliminated on the join-collapsed tree.

Leave a Reply