Page History
...
Figure 1 No statement in context error message
See “About About the Dashboard tab” on page 46, “About tab, About the Current tab” on page 55, “About tab, About the Activity tab” on page 74tab, and “About About the Objects tab” on page 105tab.
| Anchor | ||||
|---|---|---|---|---|
|
...
- Plan. The Plan view lets you display the estimated (for SQL Server 2000 and SQL Server 2005) or actual (for SQL Server 2005 only) execution plan of a statement and various related information such as statistics, referenced objects and operations performed in the execution plan. In addition, performance findings are also displayed.
- Recommend. Precise for SQL Server uses the Microsoft® Index Tuning Wizard or Database Tuning Advisor (DTA) to obtain recommended indexes or statistics for the selected statement or batch so that the optimizer will choose a better access plan and improve the performance of the statement or batch. The Recommend Indexes process only makes recommendations with regards to the addition of indexes or statistics. The What-If tab shows the number of statements whose cost would increase and the number of statements whose cost would decrease if the recommendation was implemented. It also shows the cost of implementing the recommendation so that you can weigh the pros and cons of implementing the change.
- History. The History view displays resource consumption over time vs. changes in various parameters to determine which changes led to performance problems.
- More... The The Statements view breaks down the access plan of the entire batch into statements and correlates the statements in the access plan with the statements that were captured by the Precise for SQL Server Collector. The statement id of those statements that were captured are displayed in Precise. It is possible to view the access plan of a different statement by clicking on its respective Plan icon.
...
- Highlights
- Objects
- Statistics
- More ... (includes Operations and Properties Information tabs)
| Anchor | ||||
|---|---|---|---|---|
|
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.
...
- Statements with total estimated costs greater than ‘1' 1 are checked for critical operations.
- Statement operations whose estimated cost percentage was greater than 20% of the entire statement are then labeled with a red icon.
...
- 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.
| Anchor | ||||
|---|---|---|---|---|
|
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 operators that represent TSQL statements highlight the entire statement they represent.
| Anchor | ||||
|---|---|---|---|---|
|
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.
...
| Column | Description |
|---|---|
 | Locates and highlights the operator in the execution plan tree that matches the selected operator in the grid. |
| Operator ID and Type | Displays the operator ID and type. |
| Subtree Cost | Displays the cost of the operators and its sub-operators. |
| Estimated Cost | Displays the estimated cost of the current operation. A high cost value may indicate a problem in the current implementation of the operation. If the cost value is greater than " 1", it is highlighted in red. Check the Estimated I/O cost and Estimated CPU cost values to determine whether the operation is an I/O consuming operation or a CPU consuming operation (or both). |
| Estimated Cost (%) | Displays the estimated cost (in percentage) of the current operator. |
| Estimated CPU Cost | Displays the estimated CPU cost of the current operation. A high cost value may indicate a problem in the current implementation of the operation. If the cost value is greater than " 1", it is highlighted in red. |
| Estimated I/O Cost | Displays the estimated I/O cost of the current operation. A high cost value may indicate a problem in the current implementation of the operation. If the cost value is greater than " 1", it is highlighted in red. |
| Estimated Rows | Displays the estimated number of rows returned from the current operation. |
| Avg Row Size | Displays the estimated average row size of the rows affected by current operation. |
| Parallel | Indicates whether the operator is running in parallel. |
...
Precise for SQL Server uses the Microsoft® Microsoft Index Tuning Wizard to obtain recommended indexes or statistics for the selected statement or batch so that the optimizer will choose a better access plan and improve the performance of the statement or batch. The Recommend Indexes process only makes recommendations with regards to the addition of indexes or statistics.
...
Use the What-If option to evaluate a set of recommendations on the workload containing the most common Transact-SQL statements used by the application, during the selected time frame.
To evaluate a set of recommendations using the What-if tab
...
| Column | Description |
|---|---|
 | If the object that was changed is one of the entities in the Objects tab (Instance, Database, Table and Index), the Launch icon opens the Objects tab with the selected object, in-context. Also, if the change type is "Access Plan Changed" the icon will launch the Compare View with the specified access plan compared to the last access path, as detected by the "Explain Statements" process. |
| Date | Indicates the date the change was detected. Click on the link to switch to Plan view and display the first access plan that was created by the Explain Statements process after the specified change was detected. |
| Change Type | Displays the type of change detected. Can be one of the following values:
|
| Object | Indicates the object that was changed, for example, database, table, and index. |
| Details | Displays information about the change that was made. For example if the "Auto Shrink File" option was changed in the specified database the Details column will contain: "Auto Shrink File (From NO to YES)." |
About viewing schema changes
...
| Column | Description |
|---|---|
| If the object that was changed is one of the entities in the Objects tab (Instance, Database, Table and Index), the Launch icon opens the Objects tab with the selected object, in-context. Also, if the change type is "Access Plan Changed" the icon will launch the Compare View with the specified access plan compared to the last access path, as detected by the "Explain Statements" process. |
| Date | Indicates the date the change was detected. Click on the link to switch to Plan view and display the first access plan that was created by the Explain Statements process after the specified change was detected. |
| Change Type | Displays the type of schema change detected. Can be one of the following values:
|
| Object | Indicates the object that was changed, for example, database, table, and index. |
| Details | Displays information about the change that was made. For example if the "Auto Shrink File" option was changed in the specified database the Details column will contain: "Auto Shrink File (From NO to YES)." |
About viewing estimated access plan changes
...
- Do one of the following:
- Launch to the SQL tab with a statement in context
- Open the statement you want to analyze in the SQL tab
- You can try one of the following to improve problematic operators:
- If the operator is a Table Scan, try creating an index.
- If the operator is of Join Type (such as Nested Loop, Merge, or Hash), examine a different joins method.
- If the operator is a Sort operation, check if the sort is required by the application or can be removed. For example, remove distinct or change the union clause to union all.
- If a warning is issued on the operator, try to implement the warning. For example if there is an index with no statistics perform the UPDATE STATISTICS command on the index.
See “About About the execution plan tree” on page 164tree.
| Anchor | ||||
|---|---|---|---|---|
|
...
- Do one of the following:
- Launch to the SQL tab with a statement in context, or
- Open the statement you want to analyze in the SQL tab
- On the Plan tab, click Objects.
- In the Tables Used in Plan, select the Potential Problem icon to highlight problematic operators.
- Identify the objects that are referenced by the problematic operations and try to tune those objects.
See “About About viewing which objects are referenced by the execution plan” on page 167plan.
| Anchor | ||||
|---|---|---|---|---|
|
...
- Do one of the following:
- Launch to the SQL tab with a statement in context
- Open the statement you want to analyze in the SQL tab
- On the Plan tab, click Objects.
- In the Tables Used in Plan, check if there are any local predicates defined for the table that the optimizer decided to begin the access plan with. If not, and there are local predicates defined for another table, verify that the optimizer begins the access plan with that table, since this means that less rows will be fetched during the process.
See “About About available operation options” on page 165options.
| Anchor | ||||
|---|---|---|---|---|
|
...
- Do one of the following:
- Launch to the SQL tab with a statement in context
- Open the statement you want to analyze in the SQL tab
- On the Recommend tab, observe the list of recommended indexes and statistics. For each recommended item, analyze the detailed information displayed in the recommendation details, such as a list of key columns and the DDL's “create "create index/statistic” statistic" text.
| Anchor | ||||
|---|---|---|---|---|
|
...