Original: Detailed execution plan
I. Analysis of the query execution plan
The reading order of the execution plan is, from right to left, top to bottom.
Some characteristics of the query execution represented by the execution plan are as follows:
- If the query consists of batches of multiple queries, the execution plan for each query is displayed in the order in which they are executed. Each execution plan in the batch will have a relative estimated cost, with a total cost of 100% for the whole batch.
- Each icon in the execution plan represents an operator. Each of them has a relative estimated cost, and the total cost of executing all the nodes in the plan is 100%.
- A starting operator in an execution plan typically represents a data retrieval mechanism for a database object (table or index).
- Data retrieval is typically a table operation or an index operation.
- The data retrieval on the index will be an index scan or an index super-search.
- The naming manager for data retrieval on the index [table name]. [Index name].
- Data flows from right to left two operators, represented by a connection arrow.
- The width of the connection arrows between the operators is a graphical representation of the number of transmitted rows.
- The connection mechanism between the two operators in the same column will be a nested loop connection, a hash match connection, or a merge connection.
- Prevents the cursor from displaying a pop-up window with some detail on top of a node of the execution plan.
- In the Properties window there is a complete set of details about the operator that you can right-click the operator and select Properties.
- Operator details show the type of physical and logical operations at the top. The physical operation represents the actual use of the store, while the logical operation of the optimizer is used to establish the structure for estimating the execution plan. If the logical and physical operations are the same, only physical operations are shown. It also displays other useful information, such as number of rows, I/o overhead, cpu overhead, and so on.
- The parameters section of the operator Details pop-up window is particularly useful in analysis because it shows the filter or join conditions used by the optimizer.
Ii. Identifying costly steps in the execution plan
The main interest in the execution plan is to find which steps are relatively expensive. These steps are the starting point for query optimization. You can select the steps to start by using the following techniques.
- Each node in the execution plan shows its relative overhead in the overall execution plan, with a total cost of 100% for the entire plan. Therefore, focus on the nodes that have the highest relative overhead.
- The execution plan may come from a batch of statements, so you might also need to find the most expensive statement.
- View the width of the connection arrows between nodes. A very wide connection arrow indicates that a large number of rows are transferred between the corresponding nodes. Analyze the node to the left of the arrows to understand why it requires so many rows, and also check the properties of the arrows. You may see that the estimated rows are different from the actual rows, which can be caused by outdated statistics.
- Look for the hash connection operation. For small result sets, nested loops are usually the preferred connection technology.
- Look for a bookmark lookup operation. A bookmark operation on a large result set can result in a large amount of logical reads.
- There may be a warning above the operator with an exclamation point, which is an area that requires immediate attention. These warnings can be caused by a variety of problems, including connections with no connection conditions or indexes and tables for missing statistics. It is often useful to resolve a warning condition.
- Look for steps to perform a sort operation. This means that the data is not retrieved in the correct order.
third, analysis of the effectiveness of the index
To further study the expensive steps in the execution plan, you should analyze the data retrieval mechanism for the related tables or indexes.
First, the index operation should be checked to find the scan here. Typically, for best performance, you should retrieve as few rows as possible from one table, and index lookups are often the most efficient way to access a small number of rows. Scanning operations typically represent access to a large number of rows. Therefore, generally the lookup takes precedence over the scan.
Next, you need to ensure that the indexing mechanism is correctly established. The query optimizer evaluates the available indexes to discover which indexes will retrieve data from the table in the most efficient way. If the required index does not exist, the optimizer uses the sub-optimal index. For best performance, you should always make sure that you use the best indexes in your data retrieval operations. You can determine the validity of an index by analyzing the parameters section in the node details of the following operations:
- Data retrieval operations;
- Connection operation;
Iv. Plan Cache
The last place to access the execution plan is to read directly from the memory space in which they were saved-the plan cache.
SQL Server provides dynamic management views and functions to access this data. In order to view the list of execution plans in the cache, run the following query:
SELECT p.query_plan,t.text from sys.dm_exec_cached_plans r Cross Apply Sys.dm_exec_query_plan (R.plan_handle) p cross APPLY sys.dm_exec_sql_text (r.plan_handle) t
This query returns a list of XML execution plan connections. Opening any connection will show the execution plan, and further processing of the available columns through the dynamic management view will enable you to search for a specific process or execution plan.
Detailed execution plan
