One of the main responsibilities of a database administrator is query tuning and troubleshooting query performance. In this context, SQL Server offers several tools to assist. But among them, query execution plans are essential for query optimization because they include all of the vital information about the query execution process. At the same time as it provides this valuable information “under the hood”, SQL Server creates a graphical description of the execution plan.
Query execution
When we look at the query execution; it includes 5 general steps. This diagram illustrates query execution process.
Query Parsing: The query parsing process checks the syntax of query. Said another way, query parsing process controls the T-SQL query syntax validation which we want to execute.
Binding (Algebrizer): We can describe this process, simply, as the step which checks the objects, tables or columns existing in the database. Binding (Algebrizer) creates a query processor tree and this tree will be used as input for the query optimizer.
Query Optimization: The SQL Server Query Optimizer’s task is to find the optimum execution plan. The Query Optimizer work methodology uses a cost-based optimizer which means that the Query Optimizer analyzes possible execution plans and decides optimum execution plan based on estimated cost.
Cache Execution Plan: When a query is executed and but the query execution plan was not previously stored in the plan cache, the execution engine creates a new execution plan and stores this execution in the plan cache. When the same query is executed next, SQL Server avoids creating a new execution plan and skips this step. So Query Optimizer avoids consuming redundant time and CPU. In some cases, the execution engine decides on using different execution plans. Some of the reasons leading to this situation are;
- Removing or adding an index to associated objects which are used by execution plan
- Database compatibility level changing
- Cardinality Estimation Model Version
- RECOMPILE query hint
Execution: The query is executed by the Execution Engine according to the execution plan which is decided by the query optimizer. Finally, the result of the query is returned.
Select operator
In this section, we will discuss the select operator and its main properties. The bellow image illustrates the select operator icon in the execution plan.
A query or stored procedure which consists of at least one select statement is where we can see select operators. Note that it is considered a best practices of reading execution plan, you should read execution plan right to left.
But, in some cases, before starting to analyze the execution plan you can quickly look over select operator properties, because can provide us with basic information of the query.
Now, we will discuss the main properties of the select operator.
Cached Plan Size: This value defines how much memory is consumed by the executed query in the plan cache.
Cardinality Estimation Model Version: Cardinality Estimation predicts how many rows will be returned by the executed query. In this prediction process, Cardinality Estimation uses statistic histograms. Cardinality Estimation prediction accuracy affects the quality of execution plan.
Microsoft notes that query performance can be affected by the Cardinality Estimation Model. Cardinality Estimation Model settings can be changed under the Database Scoped Configurations. At the same time the FORCE_LEGACY_CARDINALITY_ESTIMATION query hint forces the use of an earlier version of Cardinality Estimation Model Version. When we compare two queries in one of which includes FORCE_LEGACY_CARDINALITY_ESTIMATION query hint, we can see the difference between execution plans Cardinality Estimation Model Version.
Degree of Parallelism: This metric identifies how many CPUs are used to execute this query. This metric is affected by max degree of parallelism and costs threshold for parallelism settings. At the same time, MAXDOP query hint effect can affect Degree of Parallelism value.
In this section, I want to add a notice about threshold cost for parallelism setting. If your query execution plan cost does not cross the value, the query optimizer does not decide to use parallel execution plans.
The general approach about what to set cost threshold for parallelism value is between 15 and 50. But this value can change according to your system workload. Before the change, this value you have to test your SQL Server environment.
Estimated Number of Rows: This value identifies the prediction of how many rows will be returned in the select operator. As mentioned, the Cardinality Estimation Model and statistics play a significant role in this prediction accuracy.
Estimated Operator Cost: This metric identifies the percentage value of select operator as part of the total execution plan.
Memory Grant Info: This value identifies a buffer memory which is used to store temporary rows while sorting, grouping and joining rows.
Optimization Level: This metric returns information about what the SQL Optimizer did during the query optimization process. In this metric can return TRIVIAL or FULL.
TRIVIAL is the first step of query optimization. If the executed query is simple, the query optimizer decides to create a TRIVIAL execution plan. The reason for this decision that optimizer doesn’t want to spend much time to choose an effective execution plan.
FULL means the SQL Server Query Optimizer completes the query optimization cycle. But in this case, we have to observe the Reason for Early Termination of Statement Optimization property. Because this property gives us some idea about the result of the optimization process.
In the above image, the reason for Early Termination of Statement Optimization property definition is Good Enough Plan Found. It means that the query optimizer found the optimum execution plan. But in some cases you can find out that the Early Termination of Statement Optimization definition is Time Out. It means that the query optimizer attempts to find the optimum execution plan for the executed query. But this attempt reaches the limit and query optimizer stops before finding the optimum execution plan. The query optimizer decides the most optimum plan from among the various attempts until that time. In some cases, this situation is potentially a negative sign regarding query performance.
In this heading, I want to add a notice about the Query Optimizer execution plan decision algorithm. The Query optimizer goal is to find the optimum execution plan, not necessarily to find best execution plan. Some queries have a lot of possibilities for different execution plans and one of these can be best execution plan. But the Query Optimizer execution plan decision algorithm is based on a balance between optimization time and plan quality.
Parameter List: If you execute a stored procedure with parameter you will see Parameter List Property. In this property, you can find the information about the stored procedure’s first compiled parameters. In some cases, though, the compiled execution plan does not use the optimum execution plan for different parameters and this can negatively affects stored procedure performance. This performance issue is related to Parameter Sniffing.
Conclusions
In this article, we discussed the main concepts of select operators and their main properties. At the same time, we highlighted how the query optimizer works. If we want to analyze and tune the performance of a query, for best results we have to acknowledge about the query optimizer working mechanism.
References
- Understanding SQL Server Cardinality Estimations by Charting Histogram to Actual Values
- Configure the cost threshold for parallelism Server Configuration Option
- Identifying query compile/runtime parameter values using XML SHOWPLAN output
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