This article will describe the effect of SQL Server foreign keys on query plans which are created by the query optimizer.
Introduction
Foreign keys are used to enforce the referential integrity between the data of the two tables and are frequently used in relational database designs. You can see the article, What is a foreign key in SQL Server to learn more details about the foreign keys.
Along with this referential connection duty of the foreign key, it influences the created execution plans because the query optimizer has the ability to use this referential integrity information when it creates the execution plans. So that, the optimizer can generate more efficient query plans.
Pre-requirements
At first, we will create a Customers table through the following query.
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE Customers (ID INT NOT NULL PRIMARY KEY, CustomerName VARCHAR(50), CustomerAge SMALLINT, CustomerCountry VARCHAR(50) ) INSERT INTO [dbo].[Customers]([ID],[CustomerName],[CustomerAge],[CustomerCountry]) VALUES (1,N'Salvador',23,N'Brazil ') INSERT INTO [dbo].[Customers]([ID],[CustomerName],[CustomerAge],[CustomerCountry]) VALUES (2,N'Lawrence',60,N'China ') INSERT INTO [dbo].[Customers]([ID],[CustomerName],[CustomerAge],[CustomerCountry]) VALUES (3,N'Ernest',38,N'India') |
The following query will create the CustomerOrders table and the CustomerId column will be a foreign key for this table and it references the ID column of the Customers table.
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE CustomerOrders (ID INT PRIMARY KEY, OrderDate DATETIME, CustomerID INT NOT NULL FOREIGN KEY REFERENCES Customers(ID), Amount BIGINT, ) GO INSERT INTO [dbo].[CustomerOrders]([ID],[OrderDate],[CustomerID],[Amount]) VALUES (1,CAST('29-Apr-2019' AS DATETIME),1,968.45) INSERT INTO [dbo].[CustomerOrders]([ID],[OrderDate],[CustomerID],[Amount]) VALUES (2,CAST('10-May-2019' AS DATETIME),2,898.36) INSERT INTO [dbo].[CustomerOrders]([ID],[OrderDate],[CustomerID],[Amount]) VALUES (3,CAST('21-Oct-2019' AS DATETIME),3,47.01) |
SQL Server Foreign Keys and Join Elimination
For some cases, the query optimizer algorithm works pretty smartly so that it avoids redundant I/O operations and saves time for the query executions. The following methods are used by the query optimizer to improve query performances in the appropriate circumstances.
- Contradiction Detection
- Domain Simplification
- Join Elimination
- Row Goal
The SQL Server foreign keys create referential integrity between the data of the tables and the query optimizer can shape the execution plan using this referential integrity information. Join elimination is a technique that removes some unnecessary joins during the execution of the query so it decreases the I/O consumption and query execution time. Now, we will interpret the following query execution plan.
|
1 2 3 4 |
SELECT C.CustomerName , C.CustomerCountry, CU.Amount,CU.OrderDate FROM Customers C INNER JOIN CustomerOrders CU ON C.ID = CU.CustomerID |
The execution plan of the query is very simple, the storage engine retrieves the data using indexes, and then this data is joined by nested loops join operator.
Just now, we will remove the Customer tables column in the query and only retrieves the Amount and OrderData column data.
|
1 2 3 |
SELECT CU.Amount,CU.OrderDate FROM Customers C INNER JOIN CustomerOrders CU ON C.ID = CU.CustomerID |
Now, we will analyze the executed query execution plan carefully because we will see that the query plan is formed differently because of the secondary key.
One interesting point about this execution plan is that the storage engine only reads data from the CustomerOrders table and the nested loops and clustered index seek operators had been removed. The reason for this interesting situation is that the query optimizer knows that all of the CustomerID column data must be located in the ID column of the Customer table. At the same time, the executed query does not retrieve any data from the Customers table for this query optimizer for this the query optimizer thinks the join operation is redundant and removes the join operation. With this approach, the query optimizer eliminates unnecessary I/O operations and generates more efficient query plans. This method is called join elimination and generally join elimination method refers to foreign keys information.
SQL Server Foreign Keys: Trusted and Untrusted
After the bulk insert operations, check and foreign key constraints status are changed to not-trusted, if we don’t use the CHECK_CONSTRAINTS parameter. It means that the bulk copied data is inserted into the destination table without checking the referential integrity. The main idea behind this approach is to increase the performance of the bulk copy operations. However, this situation prevents the optimizer from using the join elimination method. Let’s go through the details of this case with an example. Firstly, we will look at the trust status of the CustomerOrders table foreign constraint. The sys.foreignkeys system table can give metadata information of the SQL Server foreign keys.
|
1 2 3 4 |
SELECT name, is_disabled, is_not_trusted FROM sys.foreign_keys WHERE OBJECT_NAME(parent_object_id)='CustomerOrders' |
As seen in the above image, the status is_not_trusted is 0 so we can ensure that the referential integrity exists and we can not insert any row into the CustomerOrders table that not provides the foreign key data integrity. Now, we will disable this integrity by using the following query.
|
1 2 3 4 5 6 |
ALTER TABLE CustomerOrders NOCHECK CONSTRAINT FK__CustomerO__Custo__1E6F845E; GO SELECT name, is_disabled, is_not_trusted FROM sys.foreign_keys WHERE OBJECT_NAME(parent_object_id)='CustomerOrders' |
After disabling the check constraint, we will re-execute the same query which is the join elimination method performed by the query optimizer.
The nested loops and clustered index scan operators are re-added to the query plan because of the disabled foreign key integrity. In this circumstance, the optimizer can not ensure the foreign key decided to read data from the Customer table. As a result, we can say that untrusted foreign keys ignore to use join elimination method.
SQL Server Foreign Keys with an Insert Statement
As we stated in the entrance part of the article, the foreign keys affect the query optimizer behaves and this situation is also valid for the insert statements. In the insert operations, SQL Server checks the inserted child row that exists in the parent table to verify the referential integrity. When a foreign key status is not trusted then the storage engine does not require to check the inserted foreign key is exists in the parent table. The execution plan of the following query will only include clustered index insert operator because of this situation.
|
1 2 |
INSERT INTO [dbo].[CustomerOrders]([ID],[OrderDate],[CustomerID],[Amount]) VALUES (4,CAST('21-Oct-2019' AS DATETIME),3,47.01) |
Now, we will re-enable the foreign key constraint and will change its status as trusted.
|
1 2 3 4 5 6 |
ALTER TABLE CustomerOrders WITH CHECK CHECK CONSTRAINT FK__CustomerO__Custo__1E6F845E; GO SELECT name, is_disabled, is_not_trusted FROM sys.foreign_keys WHERE OBJECT_NAME(parent_object_id)='CustomerOrders' |
After enabling the foreign key, we will insert a new row to the table and look at the interesting changes in its query plan.
|
1 2 |
INSERT INTO [dbo].[CustomerOrders]([ID],[OrderDate],[CustomerID],[Amount]) VALUES (5,CAST('21-Oct-2019' AS DATETIME),3,47.01) |
Before start talking about this new and interesting execution plan, we will briefly touch on the nested loops operator details. The nested loops operator is used to join data of the two tables and its working method is like to nested loop algorithm pattern that is used in modern programming languages. In this algorithm, a loop is placed inside of another loop. The nested loops operator makes the same thing and it takes a row from the outer table and starts to search this row in the inner table. This process continues until the nested loops operator consumes all rows of the outer table. When we look at the properties of the nested loops operator, we see that it performs a left semi join operation. At the same time, the Outer References attribute shows which column data is searched in the inner table.
The clustered index insert is performed on the outer table (CustomerOrders) for each CustomerID row an index seeks operation is performed. This case is shown in the seek predicate property of the clustered index seek operator.
The assert operator is used to verify conditions execution of the queries and its Predicate attribute shows which condition is evaluated by this operator.
Let’s explain the meaning of this predicate attribute of the assert operator. If the output of the nested loop returns a null value, the assert operator evaluates it as 0 and the query returns an error otherwise the insert operation will be performed successfully. So the insert operation is aborted and the transaction is rollbacked.
Put all the pieces together
The insert statement execution plan of the queries which involve foreign keys might be a bit complicated. For this reason, we will briefly interpret the execution plan again.
As a first step, the insert operation is completed and then the inserted foreign key value is checked for existence in the parent table. The nested loops join operator pass this result to the assert operator. The result of the assert operator reveals that the query will either pass or fail.
Conclusion
In this article, we have deep-dived into the interaction between SQL Server foreign keys and execution plans with all aspects. As we learned, foreign keys are widely used in database designs, and learning their execution plan can help to understand their performance of the queries. On the other hand, we learned trusted and un-trusted foreign keys can change the same query execution plans.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023