The Trivial Plan stage

As mentioned in the Query optimization essentials section of Chapter 2, Understanding Query Processing, SQL Server does cost-based optimization. But this has an expensive startup cost and so SQL Server will try to avoid this cost for simple queries that may only have one possible query execution plan.

The Trivial Plan stage generates plans for which there are no alternatives, and which require a cost-based decision. The following examples can be executed in the AdventureWorks sample database.

The following is a SELECT … INTO or INSERT INTO statement over a single table with no conditions:

SELECT NationalIDNumber, JobTitle, MaritalStatus
INTO HumanResources.Employee2
FROM HumanResources.Employee;

The preceding query produces the following execution plan:

The following is an INSERT INTO statement over a single table with a simple condition covered by an index:

INSERT INTO HumanResources.Employee2
SELECT NationalIDNumber, JobTitle, MaritalStatus
FROM HumanResources.Employee
WHERE BusinessEntityID < 10;

The preceding query  produces the following execution plan:

The following is an INSERT statement with a VALUES clause:

INSERT INTO HumanResources.Employee2
VALUES (87656896, 'CIO', 'M');

The preceding query  produces the following execution plan:

The information on the optimization level is stored in the execution plan under the Optimization Level property, with a value of TRIVIAL, as shown in the following screenshot:

The Trivial Plan stage typically finds very inexpensive query plans that are not affected by cardinality estimations.