Esat Erkec
Trivial query plans and contradiction detection

Explore the secrets of SQL Server execution plans

December 10, 2020 by

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.

Contradiction detection example

  • 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.

Constant scan operator details in a query

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.

As a second step, we will re-create the check constraint with NOCHECK.

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.

Check constraints and contradiction detection

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.

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.

Trivial query plans and contradiction detection

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.

A full optimized plan.

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

How to disable a trivial plan

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.

Domain simplification and execution plan

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.

Single seek and query plan

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.

Row goal feature and estimated number of rows

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.

EstimateRowsWithoutRowGoal plan attribute

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.

Row goal feature and its effects in an 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.

Comparing the query plans with the help of SSMS

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.

Esat Erkec
9,105 Views