New Analysis Wizard: Select SQL Page
View New Analysis Wizard - Select SQL Page
In the New Analysis wizard, the Select SQL page is used to select the SQL statements whose query plans you want to analyze in order to review the impact on their performance that the creation of new indexes would have.
Select SQL to check for performance changes:
Select SQL from
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.
SQL query plans to be analyzed:
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 query plans from before the analysis can be obtained two ways:
Using existing query plan saved with the SQL
This option uses the query plan that was saved with the SQL statement when it was saved in the SQL Repository or SQL Scanner.
Obtaining a new query plan under the current connection
This option retrieves the query plan with the current logon and the current database settings. This current query plan is compared to the query plan that is retrieved after the new indexes are created.
New Analysis Wizard: Index Page
In the New Analysis wizard, the Index page is used to specific the index(es) that are used for this analysis.
Query plans will be grouped under a Scenario
An individual Index Impact Analyzer consists of a group of stored SQL statements with the query plans called the "Baseline Plan." The actual Impact Analysis displays in a "Scenario." A Scenario shows the comparison information for the stored SQL statements identifying the impact on the query plan that the creation of new index will have.
Name
Enter a name for the Scenario.
Description
Enter a description.
Enter and select indexes to be analyzed
Use Add Index to add the indexes for this Scenario.
Segment
Select the segment where you would like the index created from the drop-down list.
Consumer (Default 1 Range 1-20)
Specify the number of consumer processes that should perform the sort operation for creating the index. The actual number of consumer processes used to sort the index may be smaller than the specified number, if fewer worker processes are available when Adaptive Server executes the sort.
To add an index
Click Add Index
To remove an index
Select the index and click Remove Index 
After you have created the first Scenario, you can add more Scenarios.
Add Index Wizard
The Add Index wizard is used to create the DDL for creating of the indexes for the Index Impact Analysis. This wizard is available on the Index page in the Index Impact Analyzer's New Analysis wizard.
To open this Add Index wizard
Click
| Item | Description |
| Index name | Enter a name for the index. |
|
Database |
Select the database where the index is to be created from the drop-down 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 either double-clicking the column or highlighting the column and clicking |
|
Unique |
Specify to create a unique index. |
|
Clustered/Nonclustered |
Select to create either a clustered or a nonclustered index |
.
New Analysis Wizard: Abstract Plan Page
In the New Analysis wizard, the Abstract Plan page is used to include the abstract plan with SQL statements.
Baseline Plan
Dump abstract plan
Specify whether to retrieve the abstract plan for the SQL statement for the before new indexes are created. The abstract plan displays in the right pane for SQL statements. This retrieves a new abstract plan even if one was saved with the original SQL. It is important to use this option if you originally saved the SQL statement and abstract plans in version 12.5.0.3 or earlier of Adaptive Server and are now using Adaptive Server 15.0 or later since the format of the abstract plan has changed.
Note: This option is disabled if you have selected Using existing query plan saved with the SQL on the Select SQL page.
Group name
Specify the abstract plan group name where the abstract plans are saved. The default group names in Adaptive Server are: ap_stdout and ap_stdin. These groups are usually used by the Database Administrator to enable server-wide abstract plan capturing and retrieving.
ap_stdout is used by default to capture an abstract plan.
ap_stdin is used by default to retrieve the abstract plan associated with a SQL statement during the execution of the SQL statement.
Abstract Plan Manager button
Opens the Abstract Plan Manager window to view, create, and modify abstract plan group.
Scenario
Dump abstract plan
Specify whether to retrieve the abstract plan for the SQL statement after the indexes are created. The abstract plan is displayed in the right pane for SQL statements.
Group name
Specify the abstract plan group name where the abstract plans are saved. The default group names in Adaptive Server are: ap_stdout and ap_stdin. These groups are usually used by the Database Administrator to enable server-wide abstract plan capturing and retrieving.
ap_stdout is used by default to capture an abstract plan.
ap_stdin is used by default to retrieve the abstract plan associated with a SQL statement during the execution of the SQL statement.
Abstract Plan Manager button
Opens the Abstract Plan Manager window to view, create, and modify abstract plan group.