In the New Analysis wizard, the Select SQL page is used to select the SQL statements whose access plans you want to analyze in order to review the impact on their performance that the creation of new indexes would have.
The SQL statements used in an analysis can be taken from the SQL Repository or the SQL Scanner. If you select SQL statements from the SQL Scanner, these statements are added to the SQL Repository.
Select the SQL statements for the analysis. If you select SQL statements from the SQL Scanner, the bottom portion of this section displays the SQL Repository folder so that you can select the folder where they will be stored.
In order to analyze the impact the proposed indexes might have on the performance of the SQL statements, the Index Impact Analyzer compares the query before the indexes are created to the plans after the indexes are created. The access plans from before the analysis can be obtained two ways:
This option uses the access plan that was saved with the SQL statement when it was saved in the SQL Repository or SQL Scanner.
This option retrieves the access plan with the current logon and the current database settings. This current access plan is compared to the access plan that is retrieved after the new indexes are created.
In the New Analysis wizard, the Index page is used to specify the index(es) that are used for this analysis.
An individual Index Impact Analyzer consists of a group of stored SQL statements with the access plans called the " Baseline." The actual Impact Analysis is displayed in a "Scenario." A Scenario shows the comparison information for the stored SQL statements identifying the impact on the access plan that the creation of new index will have.
|
Item |
Description | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
Name |
Enter a name for the Scenario. | ||||||||||||
|
Description |
Enter a description. | ||||||||||||
|
Add Index |
Click
| ||||||||||||
|
Remove Index |
Click |
After you have created the first Scenario, you can add more Scenarios.
The Add Index window is used to create the DDL for creating of the indexes for the Index Impact Analysis.
|
Item |
Description |
|---|---|
|
Index name |
Enter a name for the index. By default, the prefix of the index name is taken from the Index Expert settings in the Options window and then a random number is generated for the rest of the index name. Note: It is best not to change the prefix for the index. The indexes are generated as virtual indexes. If the Impact Analysis process terminates abnormally and a virtual index is left on the database, it can be easily identified as a virtual index which was created by SQL Optimizer by the prefix. |
|
Database |
Select the database where the index is to be created from the database list. |
|
Owner |
Select the owner of the index from the drop-down list. |
|
Table |
Select the table where the index is to be created from the drop-down list. |
|
Table columns |
List of all the columns from the selected table. |
|
Index columns |
Move the columns to be indexed from the Table columns list by highlighting the column and clicking |
|
Unique |
Specify to create a unique index. |
|
Clustered/Nonclustered |
Select to create either a clustered or a nonclustered index. |
The Index Impact Analysis Details window displays information about the creation of the indexes and the retrieval of the access plans. The Index Impact Analysis Details window appears after the Index Impact Analysis process is completed unless the Show details on next generation checkbox in the Index Impact Analysis Details window is unchecked.
To view the Index Impact Analysis Details window
After an Index Impact Analysis is complete, select View | Last Index Impact Analysis Details when the Index Impact Analyzer window is active.
Review this information to see if an error occurred during the retrieval of the access plan. Errors may occur if the selected SQL statements are from one schema and you have set another schema in the Schema list at the bottom left of the main window.
.
.
.