The SQL Server execution plan (query plan) is a set of instructions that describes which process steps are performed while a query is executed by the database engine. The query plans are generated by the query optimizer and its essential goal is to generate the most efficient (optimum) and economical query plan. Some query plans that are created by the query optimizer contain some interesting characteristics. In this article, we will go into details of these interesting query plans.
SQL Server Query Processing
A query goes through the following steps while it is processed by the database engine:
- Input Tree
- Simplification
- Derive Cardinality
- Trivial Plan
- Exploration
- Executable Plan
Query optimizer plays a key role in query processing and, its main purpose is to decide the most optimal query plan for the queries that wanted to execute. The optimizer estimates the resource consumption cost of the query plan candidates and it tries to choose the effective and cheap query plan in terms of I/O, memory, CPU, and time.
In the simplification phase, the query optimizer tries to transform queries into a more simple form by removing redundant and meaningless parts of the query. In this way, the queries can be executed faster using fewer resources.
Contradiction Detection
In the simplification phase, query contractions can be detected by the query optimizer. Now, let’s explain the meaning of contradiction with a very simple example. At first, we enable the actual execution plan and execute the following query in the Adventureworks database.
|
1 2 3 |
SELECT * FROM Production.Product WHERE 1 = 0; |
- Tip: Constant scan operator shows that optimizer is creating one or more rows for the next stages of the query processing
When we analyze the query plan, we see only a constant scan operator because, during the execution of the query, the Product table isn’t read by the database engine because of the 1=0 expression. This expression is evaluated as a contradiction by the query optimizer so the query returned the only columns of the Product table. There is no doubt, 1=0 expression is completely meaningless so the data engine does not require reading any on the table because this expression result will always be false. The optimizer has a smart enough algorithm to find and remove some meaningless expressions in the queries and contraction detection works as part of this algorithm.
Contradiction Detection and Check Constraints
Check constraints are used to validate a column’s data against the specified constraint definition and the check constraints can be used by the optimizer. For example, the following query will not read any data from the Product table because the CK_Product_SafetyStockLevel constraint does not allow to exist any row which is the safety stock level is lower than zero in the Product table. So that the query optimizer knows that there are not any matched rows in the table with the filter predicate because of the check constraint definition. As a result, in the execution plan of the query, we see only a constant scan operator thus the optimizer makes saving the resources.
|
1 2 3 4 5 |
SELECT Name, ProductNumber, SafetyStockLevel FROM Production.Product WHERE SafetyStockLevel = 0; |
We can create or enable the check constraints without validating existing data with the help of the NOCHECK syntax. In this circumstance, the check constraints will be flagged as not trusted. Now, we will re-create the CK_Product_SafetyStockLevel constraint with the NOCHECK syntax.
At first, we will drop the check constraint.
|
1 2 |
ALTER TABLE [Production].[Product] DROP CONSTRAINT [CK_Product_SafetyStockLevel] GO |
As a second step, we will re-create the check constraint with NOCHECK.
|
1 2 |
ALTER TABLE [Production].[Product] WITH NOCHECK ADD CONSTRAINT [CK_Product_SafetyStockLevel] CHECK (([SafetyStockLevel]>(0))) GO |
The sys.check_constraints table returns various information about the check constraints in the current database. When we look at the is_not_trusted column for the CK_Product_SafetyStockLevel constraint, we see that it has been marked as untrusted.
|
1 2 3 4 5 6 7 |
SELECT name, is_disabled, is_not_trusted, definition FROM sys.check_constraints where name='CK_Product_SafetyStockLevel' |
In this case, the query optimizer cannot trust the check constraint guidance and decided to read all records on the table to retrieve the qualified rows.
|
1 2 3 4 5 |
SELECT Name, ProductNumber, SafetyStockLevel FROM Production.Product WHERE SafetyStockLevel = 0; |
Contradiction Detection and Trivial Query Plans
In some cases, trivial plans can cause to disable the contradiction detection feature of the optimizer. Let’s examine the following query plan.
|
1 2 3 4 |
SELECT * FROM Production.Product WHERE ProductID = 1 AND ProductID = 10; |
Optimization Level attribute indicates that the optimizer generates a trivial execution for this query due to the simplicity of the query. The trace flag 8757 can use to force the optimizer to generate a fully optimized query plan. Now we will apply this flag to the query and execute it.
|
1 2 3 |
SELECT * FROM Production.Product WHERE ProductID =1 AND ProductID = 10 OPTION (QUERYTRACEON 8757) |
When all optimization steps are completed by the optimizer, the most effective query plan is found. In this way, the optimizer detects the contradiction in the query and decides that reading the Product table is redundant.
- Tip: When we make a little hocus-pocus in the query, the query optimizer generates a fully optimized query plan instead of the trivial
|
1 2 3 |
SELECT * FROM Production.Product WHERE ProductID =1 AND ProductID = 10 AND 1 IN (SELECT 1); |
Domain Simplification
As we stated, the main goal of the simplification phase is to convert the queries into a more simple form. The domain simplification tries to transform the complex predicates into the more simple predicates if it is possible. For example, the following query includes two different range predicates but when we analyze its execution plan, we will see a single predicate.
|
1 2 3 4 5 6 7 8 |
SELECT Prod.ProductID,Prod.Name ,Prod.Class FROM Production.Product AS Prod WHERE Prod.ProductID BETWEEN 326 AND 426 AND Prod.ProductID BETWEEN 226 AND 526 AND Prod.ProductID BETWEEN 426 AND 626 OPTION (QUERYTRACEON 8757) |
As we can see, in the query plan three different predicates have been reduced into a single seek.
Join Elimination
Join elimination is another technique that is used by the optimizer and it helps to simplify the queries. In this technique, the unnecessary joins are eliminated by the optimizer to improve the performance of the queries. Now, let’s tackle the following query as an example, this query will retrieve the OrderQty and ModifiedDate columns from the SalesOrderDetail table. At the same time, the SalesOrderDetail table has been joined to the SalesOrderHeader table.
|
1 2 3 4 |
SELECT Ord.OrderQty,Ord.ModifiedDate FROM Sales.SalesOrderDetail Ord INNER JOIN Sales.SalesOrderHeader OrdHead ON Ord.SalesOrderID = OrdHead.SalesOrderID |
We can figure out from the execution plan that the database engine only accesses the SalesOrderDetail and does not read any data from the SalesOrderHeader table because of the foreign key constraint on the SalesOrderID column between two tables. Because of the referential integrity optimizer avoid joining the SalesOrderHeader table.
Row Goal
Query optimizer estimates how many rows will be returned from a query and according to this information, it calculates the resource consumption of the queries. However, the query optimizer can use the row goal feature some particular keywords and behave differently than the usual:
- FAST
- TOP
- IN
- EXISTS
- SET ROWCOUNT
When the query optimizer decided to apply the row goal feature to the execution plan, the estimated number of rows will be reduced, resulting in less resource usage. Such as, when we use the TOP (n) keyword in a query, we need only n number of rows in the table so the optimizer understands this requirement and generates a plan to consider this situation.
Let’s reinforce this interesting feature with a quite simple example. The following query will retrieve only 10 rows from the Product table.
|
1 2 3 4 |
SELECT TOP 10 Name, ProductNumber, SafetyStockLevel FROM Production.Product |
In this query, the optimizer considers the TOP keyword in the query and arrange the estimated row number according to this knowledge. In the execution plan of the query, the Estimated Number of Rows and Actual Number of Rows attribute values are equal. The EstimateRowsWithoutRowGoal plan attribute will be shown when the row goal is activated and it indicates how many rows will be estimated if the row goal is not used.
We can disable row goal with the DISABLE_OPTIMIZER_ROWGOAL hint. In the following query, we will use this hint and re-analyze the execution plan.
When we compare these two execution plans, we can see the estimated number of rows and actual logical reads attributes are different. As a final word, we can say that the row goal feature can help to improve the performance of the queries but it also degrades the performance of the queries. However, row goal may affect query performance negatively, you can see the Row Goals Gone Rogue article for more details.
Conclusion
In this article, we have explored the secret behaviors of the query optimizer. The main strategy of all these features is to increase query performance and reduce resource usage. In the execution plans, these features may come across and after this article, we can easily interpret these types of 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