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. In this article, we will explore some interesting characteristics of query plans in SQL Server.

Query Processing Steps

A query goes through the following steps while it is processed by the database engine:

1. Input Tree Simplification
2. Derive Cardinality
3. Trivial Plan Exploration
4. Executable Plan

The 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 are to be executed. The optimizer estimates the resource consumption cost of the query plan candidates and tries to choose the most effective and efficient query plan in terms of I/O, memory, CPU, and time.

Contradiction Detection

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. One interesting aspect of this simplification is contradiction detection. Contradictions occur when certain expressions or conditions in the query are evaluated as always false or meaningless.

For example, consider the following query:

SELECT * FROM Production.Product WHERE 1 = 0;

In the query plan for this query, we see only a constant scan operator. This is because the query optimizer recognizes that the expression “1 = 0” is always false, and therefore, there is no need to read any data from the Product table. The optimizer is smart enough to find and remove such meaningless expressions in the queries.

Contradiction Detection and Check Constraints

Check constraints are used to validate a column’s data against a specified constraint definition. The query optimizer can also utilize check constraints to optimize query execution. For example, consider the following query:

SELECT Name, ProductNumber, SafetyStockLevel FROM Production.Product WHERE SafetyStockLevel = 0;

In this case, the CK_Product_SafetyStockLevel constraint does not allow any row with a safety stock level lower than zero in the Product table. The query optimizer knows this and realizes that there are no matching rows in the table with the filter predicate. Therefore, in the execution plan of the query, we see only a constant scan operator, resulting in resource savings.

Trivial Query Plans

In some cases, trivial plans can cause the contradiction detection feature of the optimizer to be disabled. Trivial plans are generated for simple queries that do not require complex optimization. For example, consider the following query:

SELECT * FROM Production.Product WHERE ProductID = 1 AND ProductID = 10;

The optimization level attribute indicates that the optimizer generates a trivial execution plan for this query due to its simplicity. Trivial plans may disable the contradiction detection feature. However, by making slight modifications to the query, such as adding an additional condition, we can force the optimizer to generate a fully optimized query plan.

Domain Simplification

The domain simplification phase aims to convert complex predicates into simpler ones if possible. For example, consider the following query:

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;

In the query plan for this query, we can see that three different predicates have been reduced into a single seek operation. This simplification helps improve query performance by reducing the complexity of the predicates.

Join Elimination

Join elimination is another technique used by the optimizer to simplify queries. Unnecessary joins are eliminated to improve query performance. For example, consider the following query:

SELECT Ord.OrderQty, Ord.ModifiedDate
FROM Sales.SalesOrderDetail Ord
INNER JOIN Sales.SalesOrderHeader OrdHead ON Ord.SalesOrderID = OrdHead.SalesOrderID;

In the execution plan for this query, we can see that the SalesOrderDetail table is accessed, but no data is read from the SalesOrderHeader table. This is because of the foreign key constraint on the SalesOrderID column between the two tables. The optimizer recognizes this referential integrity and avoids joining the SalesOrderHeader table.

Row Goal

The query optimizer estimates the number of rows that will be returned from a query and calculates the resource consumption accordingly. However, the optimizer can use the row goal feature for certain keywords, such as FAST, TOP, IN, EXISTS, and SET ROWCOUNT, to behave differently than usual.

For example, when we use the TOP (n) keyword in a query, the optimizer understands that we only need n number of rows from the table and generates a plan accordingly. This reduces the estimated number of rows and results in less resource usage.

Conclusion

In this article, we have explored some of the interesting behaviors of the SQL Server query optimizer. The main goal of these features is to increase query performance and reduce resource usage. By understanding these characteristics of query plans, we can better interpret and optimize our queries for improved performance.