SQL  Server's execution plan

catalogue1 Background2 Display and Save Execution Plan3 Display estimated execution plan4 Display actual execution plan5 Save execution plan in XML format6 Compare and analyze execution plans6.1 Comparison of execution plans6

1 Background

In order to be able to execute queries, the SQL Server database engine must analyze the statement to determine the most effective method of accessing the required data. This analysis is handled by a component called the query optimizer. The input of the query optimizer consists of a query, database architecture (table and index definitions), and database statistics. The output of the query optimizer is the query execution plan, sometimes referred to as the query plan or execution plan.

The query execution plan is defined as:

• The order of accessing the source table. Typically, database servers can access base tables in many sequences to generate result sets.
• The method used to extract data from each table. Usually, there are different methods for accessing data in each table. If only a few rows with specific key values are needed, the database server can use indexes. If all rows in the table are required, the database server can ignore the index and perform a table scan. If all rows in the table are required, but there is an index with key columns in it, performing an index scan instead of a table scan may save a separate result set. If the table is very small, table scanning may be the most effective method for accessing almost all tables.
• The methods used for calculation, as well as how to filter, aggregate, and sort the data in each table. When accessing data from a table, there are different methods for performing calculations on the data (such as calculating scalar values), as well as aggregating and sorting the data defined in query text, and how to filter the data.

2 Display and Save Execution Plan

The execution plan graphically displays the data retrieval method selected by the SQL Server query optimizer. Execute plan using icons instead of SETSHOWPLAN_ ALL or SETSHOWPLAN_ The table representation generated by the TEXT statement represents the execution cost of specific statements and queries in SQL Server. This graphical method is very useful for understanding the performance characteristics of queries.

Although the SQL Server query optimizer only generates one execution plan, there is a concept of estimated execution plan and actual execution plan.

• The estimated execution plan returns the execution plan generated by the query optimizer at compile time. Generating an estimated execution plan does not actually execute queries or batches, and therefore does not contain any runtime information, such as actual resource usage indicators or runtime warnings.
• The actual execution plan returns the execution plan generated by the query optimizer, and returns the execution plan after the query or batch processing is completed. This includes runtime information about resource usage indicators and any runtime warnings.

3 Display estimated execution plan

When generating an estimated execution plan, T-SQL queries or batches will not be executed. Therefore, the estimated execution plan does not contain any runtime information, such as actual resource usage indicators or runtime warnings. On the contrary, the generated execution plan displays the query execution plan that the SQL Server database engine is most likely to use when actually executing the query, and displays the estimated rows that flow through multiple operators in the plan.

To use this feature, users must have appropriate permissions to execute T-SQL queries for which graphical execution plans are to be generated, and must be granted SHOWPLAN permissions to all databases referenced by the queries.

Through SSMS, EXPLAIN, and SETSHOWPLAN_ The estimated execution plan of XML can be used for dedicated SQL pools (formerly known as SQLDW) and dedicated SQL pools in Azure Synapse Analytics.

Note: Using SETSHOWPLAN_ XML returns the execution plan information for each statement without executing it.

4 Display actual execution plan

The actual execution plan is generated after executing T-SQL queries or batch processing. Therefore, the actual execution plan contains runtime information, such as actual resource usage indicators and runtime warnings (if any). The generated execution plan displays the actual query execution plan used by the SQL Server database engine to execute queries.

To use this feature, users must have appropriate permissions to execute Transact-SQL queries that are generating graphic execution plans for them, and must be granted SHOWPLAN permissions to all databases referenced by the query.

5 Save execution plan in XML format

To execute the plan function or use the XML display plan SET option, users must have the appropriate permissions to execute the Transact-SQL query for which they want to generate an execution plan, and must be granted SHOWPLAN permissions to all databases referenced by the query.

Open SHOWPLAN using the following statement_ XML

SETSHOWPLAN_ XMLON; GO

To open the statistics XML, use the following statement:

SETSTATISTICSXMLON; GO

Execute query:

USE AdventureWorks2012;  GO  SETSHOWPLAN_ XMLON; GO  -- Execute a query.  SELECT BusinessEntityID   FROM HumanResources.Employee  WHERE NationalIDNumber = '509647174';  GO  SET SHOWPLAN_XML OFF;

6 Compare and analyze execution plans

The execution plan graphically displays the data retrieval method selected by the SQL Server query optimizer. Execute plan using icons instead of SETSHOWPLAN_ ALL or SETSHOWPLAN_ The table representation generated by the TEXT statement represents the execution cost of specific statements and queries in SQL Server. This graphical method is very useful for understanding the performance characteristics of queries.

SQL Server Management Studio includes functions that allow users to compare two execution plans (for example, the perceived good plan and bad plan of the same query) and perform root cause analysis. It also includes the ability to perform individual query plan analysis, allowing for a deeper understanding of possible solutions that may affect query performance by analyzing the execution plan of the query.

6.1 Comparison of execution plans

For troubleshooting reasons, database professionals may have to perform planned functions:

• Find the reason for the sudden slow down of queries or batch processing.
• Understand the impact of query rewriting.
• Observe how specific performance enhancement changes (such as new indexes) introduced into the architecture design effectively change the execution plan.

You can compare between the following two:

• Two previously saved execution plan files (with an extension of. sqlplan).
• An execution plan for an activity and a previously saved query execution plan.
• Two selected query plans in the query storage.

When comparing two execution plans, areas in the plan that are basically the same in execution will be highlighted with the same color and pattern. Clicking on the color area in one plan will center the other plan on the matching node in that plan. It is still possible to compare the unmatched operators and nodes of the execution plan, but in this case, the operator to be compared must be manually selected.

6.2. Analyze the actual execution plan

Troubleshooting query performance requires extensive professional knowledge in understanding query processing and execution plans, in order to effectively identify and fix root causes.

SQL Server Management Studio includes the ability to achieve some degree of automation in actual execution of plan analysis tasks, especially for large and complex plans. The goal is to more easily identify solutions with inaccurate cardinality estimates and obtain recommendations on possible mitigation measures.

summary

• The actual execution plan is generated after transaction processing SQL queries or batch execution. Therefore, the actual execution plan contains runtime information, such as the actual number of rows, resource usage indicators, and runtime warnings (if any).
• Only nodes that consider changing the plan shape will be used to check similarity. Therefore, there may be an uncolored node between two nodes in the same sub part of the plan. In this case, the lack of color means that nodes are not taken into account when checking whether the parts are equal.
• Before applying the suggested mitigation measures to the production environment, please ensure that they are properly tested.

That's all for this article about SQL Server's execution plan. For more related SQL execution plan content, please search for previous articles at Script Home or continue browsing the following related articles. We hope everyone can support Script Home more in the future!