Execution Plan in MS SQL Server

Before getting into today's main topic, let's try to guess the answer to this quiz. Of the two SQL Queries below, which SQL Query runs faster?

Note:

- payment_done, send_mrc_status, contract_number: are columns of the table Customer_contracts.

- Value of payment_done: NULL, '0', '1'.

- Value of send_mrc_status: NULL, '-4', '-3', '-2', '1', '3', '4'.

Answer: Both queries return nearly 1,500,000 records, but SQL Query 2 (~30s) runs 3x faster than SQL Query 1 (~90s).

How to know the difference between these two SQL Queries?

Well, that's what we’re gonna find out in this topic: Execution Plan in MS SQL Server.

1. What is an Execution Plan?

Imagine a roadmap detailing each step your car takes to reach its destination. The Execution Plan is similar, but instead of guiding a car, it guides your query through the data labyrinth.

It paints a picture of how the database engine processes your query, revealing:

• The specific operations involved: From table scans and joins to filters and aggregations, the plan shows each step the engine takes to execute your query.

• The estimated cost of each operation: This helps identify potential bottlenecks and understand which parts of your query consume the most resources.

• The flow of data: By visualizing the data movement, you can see how data is retrieved, processed, and manipulated throughout the execution process.

There are two types of Execution Plans to be specific:

• Estimated Execution Plan – As the name suggests, this type of Execution Plan is a prediction of the steps that will be taken by a database engine to execute a specific query. It provides valuable information about the query's efficiency and can help identify potential performance bottlenecks.

• Actual Execution Plan – The Actual Execution Plan is generated after the query has been executed. It shows the actual operations and steps involved while executing the query. This may or may not differ from the Estimated Execution Plan.

2. Key components of an Execution Plan:

• Physical Operation: These are the operators that implement the operation as directed by the logical operators. All the physical operators are usually object which perform an operation. Some examples are Clustered Index Scan, Index Seek etc.

• Logical Operation: These operators describe the actual algebraic operation that is used to process the query. Examples are Right Anti Semi Join, Hash Join etc.

• Actual Execution Mode: This is the actual execution mode that is used by the processing engine to execute the query. Examples – Row and Batch.

• Estimated Execution Mode: This is similar to Actual Execution Mode but shows the estimated value.

• Storage: This tells us how the query optimizer will store the results that are being extracted by the query.

• Number of Rows Read: This returns the total number of records that are being read by the operator from the table index.

• Actual Number of Rows: This tells us the total number of records that have been returned based on the condition in the WHERE clause.

• Actual Number of Batches: If the execution mode for the query is a batch, then it will list the number of batches being executed to fetch the results.

• Estimated I/O Cost: This tells us the cost of the input/output operations of the result set.

• Estimated Operator Cost: This is not an actual cost but relative information with respect to the other operators in the Execution Plan.

• Estimated CPU Cost: The cost that the CPU will incur in order to process the operation.

• Estimated Subtree Cost: The cost of the execution tree that is being currently read from right-to-left and top-to-bottom.

• Number of Executions: This tells us about the number of executions that the optimizer can handle in a single batch.

• Estimated Number of Executions: This is also similar to the Number of Executions just the estimated value.

• Estimated Number of Rows: The number of rows that the optimizer thinks will be returned by the operator.

• Estimated Number of Rows to be Read: The number of rows that the optimizer thinks will be read by the operator.

• Estimated Row Size: The storage size each row in the operator.

• Node ID: It is the automatic assignment of a number in the order in which the operator is called in the Execution Plan reading from right-to-left and top-to-bottom.

Additionally, if you see below, there are these three properties that tell us more about the query and the object on which the plan is generated.

• Predicate: This is the value retrieved from the WHERE clause in the SQL statement.

• Object: The table on which the operation is being performed.

• Output: The selected columns are being displayed in the result set.

Reference source https://www.sqlshack.com/execution-plans-in-sql-server/

3. Strategies for enhancement

To supercharge your Execution Plans and elevate database performance, you can consider these strategies:

• Indexing Mastery: Ensure tables have suitable indexes to facilitate rapid data retrieval.

• Statistics Updates: Keep statistics up to date to ensure accurate estimations and decision-making by the optimizer.

• Query Optimization: Review and optimize queries to facilitate the generation of efficient Execution Plans.

• Regular Analysis: Regularly analyze and monitor Execution Plans to identify opportunities for improvement.

4. Unraveling:

Regarding the quiz in the beginning, let’s find out the difference between the two SQL Queries:

• SQL Query 1: In this case, the Execution Plan only runs one node with 99% cost.

• SQL Query 2: The Execution Plan in this case runs multiple nodes and multiple parallel processes. This query has a better Execution Plan than that of SQL Query 1. So, it will run faster than the Execution Plan of SQL Query 1 with the same condition.

Summary:

• SQL Server Execution Plans are the silent architects behind efficient database operations.

• By unraveling their intricacies, interpreting their components, and implementing optimization strategies, you empower yourself to steer your database towards unparalleled performance.

• Regular analysis and optimization based on Execution Plans mark the path to a responsive and high-performing.

• As you embark on this journey, you'll find that mastery of Execution Plans is a key to unlocking the full potential of your SQL Server database.

(*) This article was written by our Software Developer - Phan Quoc Viet