Esat Erkec
SQL Server execution plan details

SQL Server Trivial Execution Plans

February 3, 2022 by

In this article, we will go through the details of the trivial execution plans and we will also tackle some examples about the trivial plans to explore effects on query performance.

Introduction

SQL is a non-procedural programing language that means the database users only code what they want to do and they can’t describe how to do the process steps. After submitting a query to the relational database engine internal units parse and compile the query and then fulfill the assigned task. At the end of all these tasks, they offer the result of the submitted query.

In terms of SQL Server, the following main steps are performed by the database engine.

Parsing -> Binding -> Query Optimization -> Query Execution

Parse is the first stage of the query processing and in this step, the query syntax is validated and then a logical parse tree is generated and it passes into the next stage. In the binding stage, the query objects’ existence and user permissions are checked, and also cached plan existence is done in this step.

Query optimization is the most complicated stage of the query executing process because in this step the query optimizer decides the execution plan of the queries so this decision directly affects the query performance. The optimization stage is divided up into substages and one of them is deciding to use a trivial plan step. In the next section of this article, we will go through the details on trivial plans and their effects on query performance.

What is a trivial query plan?

SQL Server query optimizer is a cost-based optimizer and generates different query plan candidates for a query and then it decides the most less costly plan. However, the scenario will change if the submitted query is very simple and only one possible plan is available. In this situation, the optimizer disregards the find the optimum plan because the result of this action will be only a waste the time. We call these types of plans TRIVIAL plans, which are produced by skipping the query optimization processes for the simple queries. For example, we tackle the following query. This query only filters one column in the Production table and returns the Name and ProductNumber columns.

After the execution plan of this query, we can see that the Optimization Level attribute shows the TRIVIAL. So that, we can realize that optimizer has decided on a trivial query plan for this query.

SQL Server query plan Optimization Level attribute

The sys.dm_exec_query_optimizer_info returns detailed statistics about the operation of the query optimizer. So that, we can obtain some knowledge about what the optimizer has done behind the scene. We will execute the sample query two times and will observe the occurrence column because it shows the number of occurrences of the optimization event. In order to make a proper test, firstly we need to clear the cached plan of the query. With the help of the following query, we can clear the cached query plan of our sample query.

As a second step, we will execute the following query batch.

How to use sys.dm_exec_query_optimizer_info dynamic management view

As seen clearly, the trivial plan occurrence column value has increased to one.

Trivial query plan and parallelism

SQL Server has the ability to process queries in more than one thread. With the help of this methodology, the query execution times can decrease by the database engine. Maximum Degree of Parallelism and Cost Threshold for Parallelism are the two main options that must be correctly determined when a query wants to run in parallel. When a trivial query estimated subtree cost exceeds the cost threshold for parallelism value, the optimizer passes the next optimization steps and it also considers the parallel query plans. The following query will count the SalesOrderDetailEnlarged row numbers.

Trivial query plan

When we check the Optimization Level attribute the generated execution plan is trivial and the estimated subtree cost is 21,0479. This subtree cost value does not exceed the configured cost threshold for parallelism. Now we will decrease this value under the query cost and re-execute the same query.

Configure the cost threshold for parallelism option in SQL Server

After setting the cost threshold for parallelism, we will execute the same query and look at the execution plan.

Parallel execution plan details

As we can see the plan of the query is totally changed and the optimizer foregoes the trivial and decide to generate a parallel execution plan.

Trivial query plan and check constraints

The check constraints allow defining primitive rules for the inserted and updated row values into the tables. According to the evaluation result of these definitions, the inserted and updated values are accepted or ignored in the table. The optimizer uses check constraint definitions to improve the query performance because the table could not be generated out of the check constraint rule. For example, when we execute the following query it will not perform any read operation.

Check constraints and query performance

Now, we tackle another query and the generated execution plan of this query will be trivial.

Trivial plan and check constraint

As seen in the execution plan details, when the optimizer decides to use a trivial plan, it does not consider the check constraint definitions.

Hacking the trivial plans

In some cases, we want to discard usage of the trivial plans by the optimizer. To do this, we can use a trace flag. The trace flag 8757 disables the generation of the trivial plan. In the following query, we will use the trace flag 8757 and analyze the changing of the execution plan.

How to disable a trivial plan with trace flag

The execution plan shows us, optimizer completes the query optimization cycle and then finds an optimum plan instead of the trivial plan. As an alternative method, we can add the 1=(SELECT 1), 1 IN (SELECT 1), or EXISTS(SELECT 1) expressions at the end of the query.

How to disable a trivial plan with an expression

The ENABLE_PARALLEL_PLAN_PREFERENCE query forces optimizer to generate a parallel plan. Some little touches can convert a trivial plan to a parallel query plan.

Parallel plan vs. Trivial Plan

Trivial plans and statistics

Statistics is one of the vital inputs of the query optimizer to calculate the estimated cost of the queries. If the Auto Update Statistics option is configured ON in a database, the optimizer automatically updates the statistics during the execution of the query when the modification counter of statistics exceeds a threshold value. This approach helps to improve the performance of the queries because the optimizer makes more proper estimations. On the other hand, updating the stale statistics during the execution of the query can lead to a waste of time. After this basic information, we will look at the behavior of the trivial plan.

At first, we will create a very simple table and populate some sample data into it.

As a next step, we will generate an extended event session to track the statistics update operations. The auto_stats can capture when an automatic updating statistics event occurs. The following query will create this event.

We start the monitor the extended event captured data to use the Watch Live Data option. Now we will execute the following query.

Capturing stats creation actions with an extended event

In the first execution of the query, a statistic is created and the optimizer has used this statistic.

Trivial plans and statistics

Now we will insert some more rows into the table. After the insert operation, we expect that the stats must be updated.

We execute the same query again and directly look at the extended event data but we could not see any new event. Actually, the reason for this issue is very clear, the trivial plan does not update statistics.

At the same time, the query plan indicates that there is a huge difference between the estimated and actual number of rows.

Reading an query plan

As the last step, we will discard the usage of the trivial query plan adding the 1=(SELECT 1) expression, and re-execute the query.

Use the Watch Live Data option in Extended Events

The statistics have been updated when we disable the trivial plan and also the estimated and actual row attributes numbers show the same numbers.

SQL Server execution plan details

Summary

In this article, we focused on the trivial execution plan details with all aspects and we also learned side effects on the query performance.

Esat Erkec
1,263 Views