Esat Erkec
Clustered index seek operator details of a Transact-SQL query.

Interpreting execution plans of T-SQL queries

June 12, 2020 by

In this article, we will analyze a simple T-SQL query execution plan with different aspects. This will help us to improve our practical skills instead of discussing theoretical knowledge.

The execution plan is a very significant point to figure out what is going on behind the query execution process. For this reason, if we want to boost the performance of a query in which we experience poor performance, we need to understand clearly what this guide tells us. In fact, execution plans are an output of the query optimizer, so we’ll try to understand how the query optimizer behaves. Firstly, let’s go over some of the main concepts that will be used in this article.

What is Transact-SQL (T-SQL)?

SQL stands for Structured Query Language and is designed to query the database system, and it is also compliant with the ANSI standard. However, Transact-SQL (T-SQL) is an advanced form of the SQL language that is particularly used to query Microsoft SQL databases.

What is a query optimizer?

The query optimizer is a very crucial component of the database engine that analyzes queries and tries to generate effective (optimal) execution plans. Every executed query will consume resources, such as I/O, CPU, Memory. The optimizer calculates the estimated cost of these resources and chooses an optimum query plan based on this calculation.

What is an Execution Plan?

The execution plan can be thought of as a flight data recorder that tells us the execution details of a query. The actual execution plan generated after a query includes detailed information about the runtime metrics. On the other hand, a query optimizer can create a query plan based on the estimation method, and this query plan is called an Estimated Execution Plan.

How to display an execution plan?

We can enable the Actual Execution Plan of a query on the SQL Server Management Studio (SSMS) toolbar. Only we need to click the Include Actual Execution Plan button, as shown below, or we can press the Ctrl+M key combination at the same time.

Enable the Actual Execution Plan on SSMS

After the execution of the query, we can see the actual execution plan.

Query plan of a Transact-SQL query.

Pre-requisites

In this article, we will use a very simple table that can be created through the following T-SQL query.

Tip: Primary Key constraint is a column or set of columns that uniquely identify the rows in the table. When we create a Primary Key constraint on the table, the clustered index will be created automatically for the underlined table if we don’t explicitly specify it as a unique nonclustered index. You can refer to Learn SQL: Primary Key article to learn more details about the primary key.

I used ApexSQL Generate to generate 1M test data for this table, and it only takes 2.27 seconds.

Generate 1M test to using ApexSQL Generate

What is the clustered index seek and clustered index scan operators?

A clustered index stores data of the rows’ in a logical sorted structure, so it is a very efficient way to read the rows using the clustered index structure. This data accessing method is called a clustered index seek. For example, the following query pinpoints the qualified rows using the clustered index seek operator.

Clustered index seek operator details of a Transact-SQL query.

When we hover over the clustered index seek operator, a pop-up form will appear that involves detailed information about the index seek operator. In this form, the Number of Rows Read property indicates the total number of the rows which are read by the operator.

Number of Rows Read property of the clustered index seek operator.

Particularly for this T-SQL query, the “Number of Rows Read” property value and the “Actual Number of Rows” property value is equal, so the index seeks operator has not performed unnecessary read. As a result, we can say that the clustered index seek operator is an efficient way to read the rows.

On the other hand, the query optimizer reads all clustered index pages to find the appropriate rows, and this operation is called a clustered index scan. Now we will execute the following query and analyze the execution plan.

Clustered index scan operator details of a Transact-SQL query.

When we take a glance at the Number of Rows Read attribute value of the clustered index scan operator, we will see that this operator reads 1M rows, which means it reads the whole rows of the tables, but it only transfers 62000 qualified rows into the next operator.

The number of Rows Read property of the clustered index scan operator.

As a result, we can say that the clustered index scan operator touches every row of the table.

What is a trivial execution plan?

Query optimizer decides to create a trivial execution plan when a query is pretty simple so that it avoids consuming time to find out an effective execution plan. When we re-analyze the above query plan of the above query, the optimizer has generated a trivial execution plan. The Optimization Level attribute value shows clearly this situation.

Trivial execution plan details of a Transact-SQL query

In fact, query optimizer valid reason to create a trivial query plan because it has only one option to use to access the data of the TestTable. However, a trivial plan does not offer any missing index recommendations because it skips some of the query optimization phases. Trace flag 8757 can be used to eliminate trivial execution plan and force the optimizer to complete the full cycle of the query optimization process. Now we will use trace flag 8757 to our example T-SQL query.

Missing index details and trivial execution plan

What is the Cardinality Estimation?

The query optimizer uses statistics to estimate how many rows are returned from each operator of the query plan. According to this estimation, optimizer calculates the total cost of the (CPU, I/O, Memory) T-SQL query. In this context, the more accurate estimations help to obtain effective query plans for the optimizer. The CardinalityEstimationModelVersion indicates the version of the cardinality estimator. For our example, CardinalityEstimationModelVersion value is 140, and it shows we are executing the query under the SQL Server 2017.

Property of the CardinalityEstimationModelVersion

What is the parallel execution plan?

The query optimizer can process a query that requires excessive workload in discrete threads. In this approach, the main idea is to handle more rows in unit time, so it might reduce the query execution time. For our example of the T-SQL query, the query optimizer creates a serial execution plan, but we can force it to create a parallel execution plan with the help of the ENABLE_PARALLEL_PLAN_PREFERENCE hint.

How to use ENABLE_PARALLEL_PLAN_PREFERENCE hint

In the above image, we can see that how many rows have been read by each thread. The Gather Stream operator merges all these multiple thread inputs into the single output.

Gather Stream operator

Find some more secret about execution plans

As we stated, the execution plan includes too much information about the query execution backstage. When we observe the select operator of the sample query, we will see an OptimizerStatsUsage property. When we expand it, we can see which statistics are used by the optimizer.

The OptimizerStatsUsage property details.

The LastUpdate property shows when statistics were last updated, and SamplingPercent indicates how many percents of rows of data are using to create histograms. When we update statistics with the FULLSCAN option, it computes statistics by searching all rows in the table.

Now, we will execute the sample query again and analyze the execution plan.

Understanding SamplingPercent details of the execution plan.

As we can see, the SamplingPercent attribute value has changed after updating the statistics.

Conclusion

In this article, we discussed the execution plan details of a very simple T-SQL query. Understanding the execution plan of a query is the starting point to solve the performance issues of the queries. For this reason, in this article, we have interpreted a very simple query execution plan to improve practical experience. Let’s quickly recall the below that we have learned in this article:

  • Clustered index seek and clustered index scan operators
  • Trivial execution plans
  • Parallel execution plans
  • Cardinality Estimation
  • How to find out which statistics were used during query execution

Esat Erkec
778 Views