SQL Server Trivial Execution Plans

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.

1 2	SELECT Name,ProductNumber FROM Production.Product WHERE ProductNumber = 'CA-6738'

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.

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.

DECLARE @PlanHandle VARBINARY(64);

SELECT @PlanHandle=cp.plan_handle

FROM sys.dm_exec_cached_plans cp

OUTER APPLY sys.dm_exec_sql_text(cp.plan_handle) st

OUTER APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

WHERE st.TEXT LIKE '%Production%ProductNumber%'

AND st.TEXT NOT LIKE '%dm_exec_cached_plans%';

DBCC FREEPROCCACHE(@PlanHandle)

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

SELECT * FROM sys.dm_exec_query_optimizer_info WHERE counter = 'trivial plan';

SELECT Name,ProductNumber FROM Production.Product WHERE ProductNumber = 'CA-6738';

SELECT * FROM sys.dm_exec_query_optimizer_info WHERE counter = 'trivial plan';

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.

1	SELECT COUNT(*) FROM Sales.SalesOrderDetailEnlarged

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.

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE

EXEC sp_configure 'cost threshold for parallelism', 10;

RECONFIGURE

EXEC sp_configure 'cost threshold for parallelism'

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

1	SELECT COUNT(*) FROM Sales.SalesOrderDetailEnlarged

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.

1	select UnitPrice from Sales.SalesOrderDetail WHERE UnitPrice =-1

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

1 2	SELECT CarrierTrackingNumber, UnitPrice FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43659 AND UnitPrice =-1

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.

SELECT CarrierTrackingNumber, UnitPrice

FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43659

and UnitPrice =-1

OPTION (QUERYTRACEON 8757)

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.

SELECT CarrierTrackingNumber, UnitPrice

FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43659

AND UnitPrice =-1

AND 1 IN (SELECT 1)

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.

SELECT COUNT(*) FROM Sales.SalesOrderDetailEnlarged

WHERE 1 IN (SELECT 1)

OPTION (QUERYTRACEON 8757,USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))

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.

CREATE TABLE TestTrivialTable(IdNo INT IDENTITY(1,1),AnyNameColumn VARCHAR(10))

INSERT INTO TestTrivialTable

VALUES('TestValue')

GO 5000

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.

CREATE EVENT SESSION [CaptureStatsUpdate] ON SERVER

ADD EVENT sqlserver.auto_stats(

ACTION(sqlserver.sql_text)

WHERE ([sqlserver].[session_id]=(72)))

WITH (TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

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

1	SELECT * FROM TestTrivialTable WHERE AnyNameColumn='TestValue'

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

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

INSERT INTO TestTrivialTable

VALUES('TestValue')

GO 10000

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.

1	SELECT * FROM TestTrivialTable WHERE AnyNameColumn='TestValue'

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

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.

1 2	SELECT COUNT(*) FROM Sales.SalesOrderDetailEnlarged WHERE 1=(SELECT 1)

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

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.

Author
Recent Posts

Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

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