When optimizing SQL Performance, we often use viewing the execution evolution. The simplest thing is to use Ctrl + L to view the execution plan, however, the first time I saw this interface, my friends may not be able to get started. SQL server uses icons to display the SQL statement execution process.
First of all, we need to understand the meaning of each icon, limited by space, here is not detailed, click to view the official explanation of msdn.
Next, when we move the mouse over each icon, some data will appear. The data is explained as follows (click to view the msdn description ):
tooltip |
description |
physical operation |
physical operators used, such as hash join or nested loops. The red physical operator indicates that the query optimizer has issued a warning, for example, missing column statistics or missing join predicates. This may cause the query optimizer to select a query plan that is less efficient than expected. For more information about column statistics, see use statistics to improve query performance. when we recommend that you create statistics, update statistics, or create indexes for the graphic execution plan, you can use the shortcut menu in the object Resource Manager of SQL Server Management studio to immediately create or Update Missing column statistics and indexes. For more information, see the topic of the index operation guide. |
logical operation |
logical operators that match physical operators, such as inner join operators. Logical operators are listed behind physical operators, both of which are at the top of the tooltip. |
estimated row size |
the estimated size (in bytes) of the row generated by the operator ). |
estimated I/O cost |
estimated overhead of all I/O activities used to perform operations. This value should be as low as possible. |
estimated CPU cost |
estimated overhead of all CPU activities used to perform the operation. |
estimated Operator cost |
overhead of the query optimizer used to perform this operation. The overhead of this operation is displayed in parentheses as a percentage of the total overhead of the query. Because the query engine selects the most efficient operation to execute the query or execution statement, this value should be as low as possible. |
estimated subtree cost |
total overhead of all operations performed by the query optimizer and before this operation in the same subtree. |
Estimated number of rows 1 |
Number of rows generated by the operator. |