Page History
...
The Compare view enables you to compare access plans of a specified statement. By default, the last access plan is displayed in the left pane. The access plan you want to compare it to is displayed in the right pane. The statement's text is displayed at the bottom of each access plan pane and the selected operator is highlighted.
About viewing the execution plan of a statement
The Plan view lets you display the execution plan of a statement and various related information such as statistics, referenced objects and operations performed in the execution plan. It also displays a list of findings for the specified statement or batch.
...
- Highlights
- Objects
- Statistics
- More... (includes Operations and Properties Information tabs)
About the execution plan tree
The Plan tree displays the execution plan tree of the specified statement. When you analyze the access plan of a selected statement, you can examine the access path that was chosen by the SQL Server Optimizer. The optimized text that was generated by the SQL Server is displayed instead of the original text of the statement. The Explain result is displayed as an explain tree. Each branch in the explain tree represents one operation.
...
Each operation is displayed in the following format:
[<execution order>] <operation type> (<options>) <accessed object> [<cost in percentage>]
A red (critical) icon will appear for a problematic operation. The color of the icon will change depending on the estimated operation costs and predefined thresholds.
...
It is also possible to display the Execution plan's formatted text, at the bottom of the Execution Plan Tree. The text that relates to the selected step in the plan tree is highlighted. This allows you to view the text of the statement, the execution plan and additional information, such as the objects referenced by the statement, all at once.
About actions that can be performed on the tree
The following actions can be performed on the execution plan tree:
- The Playback controls, located at the top of the tree enable you to freely move within the execution plan of an explained statement.
- By moving the pointer over the execution plan steps you can view a ToolTip that contains statistical information, such as, Estimated Cost and Average Row Size of the specified step.
- Selecting a specific step will highlight its sons and affect the information displayed in the information tabs.
- Clicking on the plus sign (+) located at the bottom of the tree, displays and highlights the statement's formatted text.
About available operation options
The options available for an operation are determined by operator type, operator arguments, and operator predicates. Precise for SQL Server enables you to obtain a reasonable understanding of the execution plan selected by the SQL Server Optimizer by displaying the operation's options on the execution plan tree.
The following options are available for different operations:
- Clustered Index. The leaf pages of the index hold the actual data, instead of RIDs (Records ID). A table without a clustered index is called a Heap.
- Non-clustered Index. The index consists only of the columns that combine the key and a pointer to the records in the table data. If the table has a clustered index, then the pointer is the key of the clustered index. If the table does not have clustered index, then the pointer is an RID.
- Seeking. The index tree can be used for quickly locating the matching records. Seeking can be done only on indexes.
- Scanning. The leaf pages of the index or the data pages of the table are consecutively scanned.
...
| Info |
|---|
Seeking is the recommended way to use indexes. |
The following table describes the available operation options.
Table 10-2 Operation options
| Operation |
|---|
...
| Description |
|---|
...
| Sort |
...
The Sort operator presents the sorting of the rows returned from the previously executed operation (appears just below this operator) in a specific order, optionally eliminating duplicate entries. Sorting is required when no index that satisfies the requested ordering exists, or when an index scan is more expensive than sorting. It is usually used as the final step to retrieve the fetched data or a prior step for the join or stream aggregate (group by) operators. |
...
In some cases, the sort operation requires more than the available memory, in which case a temporary table is used. This involves disk I/O, which impacts performance. |
...
Sorting a large number of rows makes heavy use of the CPU, which can also affect overall server performance. The amount of memory needed for sorting can be estimated by multiplying the number of rows by the average row size. Therefore, minimizing the row size by selecting only the necessary fields will decrease the memory required for the sort operation. |
| Hash |
...
| joins | A hash join algorithm is used to access a record directly, by creating a hash function. A hash function uses a key as input and returns a hash value, which is then used as a pointer to locate the row directly. There may be different keys with the same hash value. A good hash function almost never returns the same hash value for different keys. Hash join algorithms are used as follows: |
...
|
...
|
...
| |
| Nestled joins | Nested |
...
joins (also called nested loops) use the nested algorithm to join two tables. The nested algorithm works as follows: For each row in the outer table (the first table, appearing directly below the nested loop operator in the explain tree), SQL Server executes the access plan for the inner table (the second one). A large number of iterations may indicate a problem in choosing the access plan due to out-of-date statistics. |
| Merge |
...
| joins | A merge join algorithm is used to join two tables by merging them. Merge join algorithms read both tables only once; therefore it is required that the input be sorted according to the join predicates. |
About viewing the text and performance findings for a statement
The Highlights tab displays the Statement's formatted text and any performance findings identified for the selected statement. The text that relates to the selected step in the plan tree is highlighted. The highlighted color is based on the selected operator type. The formatted text of the selected statement is displayed in the right pane.
When a table is highlighted, all the table's columns, appearing in the text, are highlighted in the same color as the table. The following is an explanation of the major operator types.
About Operators that access a table or index
The table and its columns are highlighted and color-coded. The columns that accessed the table or index are underlined.
About sort operators
All tables whose columns were used by sort operators are highlighted and color-coded. Each table and its corresponding columns have their own color. The columns that participate in the sort are underlined.
About join operators
The methodology for highlighting nested loop operators differs slightly from that of the merge join and hash join. Nested loop operators are highlighted as follows—all tables and their corresponding columns participating in a sub-tree of the outer table (the first operator that is a direct descendant of the nested loop operator) are highlighted in blue; all tables and their corresponding columns participating in a sub-tree of the inner table (the second operator that is a direct descendant of the nested loop operator) are highlighted in red.
The highlighting methodology for the other join operators (hash join and merge join) differs slightly. The first table and all its corresponding columns are highlighted in blue. The columns that used for the join are underlined. The second table and all its corresponding columns are highlighted in red. The columns that were used for the join are underlined.
About TSQL operators
The operators that represent TSQL statements highlight the entire statement they represent.
About viewing which objects are referenced by the execution plan
The Objects tab displays three tables (Tables used in Plan, Indexes of Table, Columns of Table) that list all referenced objects in the execution plan, including their indexes and columns. Statistical details and general details are displayed for each object and its sub-entities.
About tables used in plan
Displays a list of all the referenced tables in the tree.
The following table describes the information displayed for the referenced tables.
Table 10-3 Tables in use
| Column |
|---|
...
| Description |
|---|
...
 |
...
| Click to launch to the Objects tab with the selected table in context. |
 |
...
| Click the Locator icon to locate and highlight all the operators in the execution plan that access the specified table. |
...
| Used | Indicates whether the specified table is used in the selected operator in the execution plan tree. |
...
 | The Potential Problem icon indicates there is a potential problem in the specified table. A potential problem can be one of the following: |
...
|
...
|
| Table |
...
| Displays the full table name in the following format: ([Database Name].[Owner].[Table Name]). |
...
| Cost | Total cost of the access of the steps to the specified table. |
| Cost (%) |
...
| Total cost in percentage of the access of the steps to the specified table. | |
| Rows |
...
| Number of rows in the specified table. |
| Pages |
...
| Allocated | The number of pages allocated in the disk for the table records, index records, and text or image data. The value might be incorrect due to out-of-date space usage information. If you suspect the value to be incorrect, use the See SQL Server Books Online for |
| Pages |
...
| Used | The number of pages used in the disk for the table records, index records, and text or image data. The value might be incorrect due to out-of-date space usage information. If you suspect the value to be incorrect, use |
...
the See SQL Server Books Online for |
| Have Local |
...
| Predicates | Indicates whether the specified table has local predicates in the execution plan. |
About indexes defined on a selected table
Displays a list of all the indexes of the selected table displayed in Tables in use.
...