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.
After the execution of the query, we can see the actual execution plan.
Pre-requisites
In this article, we will use a very simple table that can be created through the following T-SQL query.
|
1 2 3 4 5 6 |
CREATE TABLE TestTable ( Id INT PRIMARY KEY IDENTITY(1, 1), CityName VARCHAR(100), CountryName VARCHAR(100)) |
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.
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.
|
1 2 |
SELECT CityName , CountryName from TestTable WHERE Id BETWEEN 2000 AND 30000 |
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.
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.
|
1 2 |
SELECT CountryName from TestTable WHERE CountryName LIKE 'A%' |
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.
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.
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.
|
1 2 3 4 |
SELECT CountryName FROM TestTable WHERE CountryName LIKE 'A%' OPTION(QUERYTRACEON 8757); |
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.
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.
|
1 2 3 |
SELECT CountryName FROM TestTable WHERE CountryName LIKE 'A%' OPTION(QUERYTRACEON 8757, USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE')); |
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.
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 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.
|
1 |
UPDATE STATISTICS TestTable WITH FULLSCAN |
Now, we will execute the sample query again and analyze the execution plan.
|
1 2 |
SELECT CountryName from TestTable WHERE CountryName LIKE 'A%' |
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
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
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023