Page History
...
Table 10-18 Compare statements table
| Column |
|---|
...
| Description |
|---|
| Plan |
...
| Time | Indicates when the specified access plan was performed. |
| Plan |
...
| Type | Indicates whether the specified access plan is an estimated or actual access plan. |
| Estimated |
...
| Cost | Displays the estimated cost of the specified access plan. |
| Table |
...
| Scans | Indicates the number of table scans performed in the specified access plan. |
| Index |
...
| Scans | Indicates the number of index scans performed in the specified access plan. |
| Parallel |
...
| Access | Indicates the number of parallel operations performed in the specified access plan. |
| Nested |
...
| Loops | Indicates the number of nested loops performed in the specified access plan. |
| Merge |
...
| Joins | Indicates the number of merge joins performed in the specified access plan. |
| Hash |
...
| Joins | Indicates the number of hash joins performed in the specified access plan. |
...
| Sorts | Indicates the number of sort operations performed in the specified access plan. |
| Info |
|---|
...
It is also possible to access the Compare View from the Access Plan Changes table. Click the Compare icon in the Access Plan Changes table to open the Compare view with the selected access plan, in-context, and compare it with the last access plan. |
About breaking down a batch's access plan into statements
In many cases a statement is part of a long batch (such as sp or adhoc batch). At times you will want to examine a particular statement, and at times you will want to view the statement in the context of the entire batch.
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.Note:
| Info |
|---|
Only statements that were captured by the Precise for SQL Server Collector can be chosen. |
When you switch to this view in the context of a batch, all the batch's statements are listed. In this case, all the information displayed is related to the selected batch.
...
Table 10-19 Statements view table
Column Description
...
| Column | Description |
|---|---|
 | Switches to Plan view, with the selected statement in-context. This icon is only enabled at the batch level and in the statement that was captured by the Collector. |
...
| ID | Indicates the ID of the statement in the batch. |
| Collector |
...
| Statement | Displays the ID of the statement and batch as assigned by the Precise for SQL Server collector. Click on the link to display the plan after the change occurred. |
| Physical |
...
| Operator | Describes how a query or update was executed. The physical operator describes the physical implementation used to process a statement, for example: Batch level, Select, Update, Cond, etc. |
...
| Operations | Displays the number of operations in the specified statement. |
| Table |
...
| Scans | Displays the number of Table Scans performed in the specified statement. |
| Index |
...
| Scans | Displays the number of Index Scans performed in the specified statement. |
...
| Joins | Displays the number of join operations performed in the specified statement. |
| Sorts |
...
| Displays the number of sort operations performed in the specified statement. |
| Estimated |
...
| Cost | Displays the estimated cost for the specified statement. |
| Anchor | ||||
|---|---|---|---|---|
|
...
- Create a new statement
- Open an existing statement
- Edit an existing statement
- Re-explain an access plan
- Display the current estimated access plan using Microsoft SQL Server Management Studio (for SQL Server 2005) or Query Analyzer (for SQL Server 2000)
Creating a new statement
You can create a new statement and save it in the PMDB in a logical cabinet and folder hierarchy. You can also rewrite statements and view their access plans.
...
- On the Actions menu, click New.
- In the New Statement dialog box, choose the relevant properties for the new statement from the drop-down lists and enter a statement name, as follows:
- Instance. Indicates the name of the instance that the statement belongs to.
- Cabinet. Indicates the name of the cabinet that the statement is saved in (creates a cabinet if it does not already exist).
- Folder. Indicates the name of the folder that the statement is saved in (creates a folder if it does not already exist).
- Name. Indicates the name of the statement (generated randomly).
- Database. Indicates the name of the database where the statement is running.
- User. Indicates the statement's parsing user.
- Type the SQL text for the statement in the Text frame.
- Click OK.
The new statement is saved in the PMDB in a logical cabinet and folder hierarchy.
Opening an existing statement
You can view a statement that was saved in the through the New tuning action or through the Save As option in the Current tab.
...
- On the Actions menu, click Open. The Open Statement dialog box is displayed.
- In the Open Statement dialog box, choose the relevant properties for the statement you want to view from the Instance, Cabinet, Folder and Name drop-down lists, as follows:
- Instance. Indicates the name of the instance that the statement belongs to.
- Cabinet. Indicates the name of the cabinet that the statement is saved in.
- Folder. Indicates the name of the folder that the statement is saved in.
- Name. Indicates the name of the statement.
- Database. Indicates the name of the database where the statement is running.
- User. Indicates the statement's parsing user.
- Click OK.
Editing an existing statement
You can edit a statement that was saved in the through the New tuning action or a statement that was collected by the Precise for SQL Server Collector. The statement that was collected by the Collector is saved as a new statement in a logical cabinet and folder hierarchy.
...
- On the Actions menu, click Edit.
- In the Edit Statement dialog box, select the statement you want to edit by choosing its relevant properties from the drop-down lists, as follows:
- Instance. Indicates the name of the instance that the statement belongs to.
- Cabinet. Indicates the name of the cabinet that the statement is saved in.
- Folder. Indicates the name of the folder that the statement is saved in.
- Database. Indicates the name of the database where the statement is running.
- User. Indicates the statement's parsing user.
- Enter a new statement name in the Name field.
- Edit the SQL text for the statement in the Text frame.
- Click OK.
The edited statement is saved in a logical cabinet and folder hierarchy.
About re-explaining an access plan
You can generate a new estimated access plan, save it, if it is different from the most recent access plan, and display the most recent access plan.
About displaying the current access plan using Microsoft SQL Server Management Studio or Query Analyzer
You can display the current estimated access plan using Microsoft SQL Server Management Studio (for SQL Server 2005) or Query Analyzer (for SQL Server 2000).
...
Performance tuning examples:
- Identifying problematic operators in the latest access plan
- Locating referenced tables that pose potential problems
- Locating tables with local predicates that are not being used efficiently in the access plan
- Examining how schema and configuration changes affected statement performance
- Comparing statement access plans changes that led to performance changes
- Recommending Indexes or Statistics procedures on a selected statement
- Locating the most resource-consuming statements in a batch
| Anchor | ||||
|---|---|---|---|---|
|
The first step in tuning a statement is to identify problematic operators. Problematic operators are operators whose estimated cost is high or operators that have caused warnings, such as ‘index with no statistics', to be issued.
| Info |
|---|
...
The lower the estimated cost the better. In most cases an estimated cost that is greater than or equal to 1 is considered high and should be improved. |
To identify problematic operators in the latest access plan
...
See “About the execution plan tree” on page 164.
| Anchor | ||||
|---|---|---|---|---|
|
When it is not possible to access the statement, such as in third company products, the only way to improve the statement is by improving access to the data. This can be achieved by performing changes in the referenced objects by examining the operators that access the tables and identifying potential problematic operators.
...
- 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 viewing which objects are referenced by the execution plan” on page 167.
| Anchor | ||||
|---|---|---|---|---|
|
If you are familiar with the application and statements and are confident in your understanding of how the access plan should be executed, you may want to examine the access plan to check if the optimizer accesses the tables in proper order. When joining tables, the fewer the rows that need to be joined, the faster the join operation will be executed. The optimizer selects which table to access first according to the join conditions of the other tables. Making the incorrect decision regarding which table to start with, can lead to a badly tuned statement, since more rows need to be joined.
There are two kinds of predicates: Local and Join.
- Local
...
- predicates. Columns in the
Whereclause are compared to a constant or to a bind variable. (For example: t1.id = 30). - Join
...
- predicates. Conditions that join tables. A column in table A is compared to a column in table B. (For example: t1.id = t2.id).
To locate tables with local predicates that are not being used efficiently in the access plan
- 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 available operation options” on page 165.
| Anchor | ||||
|---|---|---|---|---|
|
Changing schema and configuration parameters can affect the performance of your application. Precise for SQL Server allows you track the schema and configuration changes and compare them with the resource consumption of your statement to understand how the changes related to the statement affected its performance.
...
- 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 History tab, on the View controls, click History.
- Analyze the statement's resource consumption over time vs. execution cost, schema changes and number of executions over time. This can help you understand what caused the In MS-SQL data to change and to determine whether it was caused by a change that was made or by the frequency with which the statements were executed. If, for example, changes were made to the schema, examine the time the changes were reported and the time the statement's performance began to improve. This can lead you to locating the changes that led to improved statement performance.
- On the Change History controls, in the Change History area, you can view additional details on the changes made. You can select the launch icon for a particular change and launch to the Objects tab in context, to continue your analysis of the change.
| Anchor | ||||
|---|---|---|---|---|
|
If you conclude that a change was made to the access plan you can carry your analysis of the change further by selecting the Compare tab.
...
- 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 Compare tab, compare two access plans for the same statements or batch. Select an operator in one access plan. The corresponding operator in the other access plan is also selected and the statement text is highlighted.
| Anchor | ||||
|---|---|---|---|---|
|
At times you may want to run the Microsoft Index Tuning Wizard to help you tune a statement. This can be done in the Recommend view.
...
- 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 index/statistic” text.
| Anchor | ||||
|---|---|---|---|---|
|
The main difference between examining a statement's access plan and a batch's access plan is in the number of operators in the access plan. Understanding an access plan with hundreds of operators is a difficult task. The Statements view can assist you with this task since it shows the access plan statements and their estimated cost. The ID of the statements sampled by the Precise for SQL Server Collector are also displayed.
...