SQL Optimizer for SAP ASE 3.9 - User Guide

Configuration Analyzer Tutorial

The Configuration Analyzer evaluates the effect on SQL performance when changing Adaptive Server parameter settings. It enables you to analyze whether the database performance may improve before you make configuration parameter changes permanent.

1. Click .

2. Click . If this is your first time in the Configuration Analyzer, the New Analysis wizard automatically opens.

Analyzer Page

3. In the New Analysis wizard, under the Analyzer page, specify if you want to check the effects of configuration changes by either Creating a new Analyzer or Continuing an existing Analyzer.

4. Give the analysis a name and description for easy reference.

5. If you would like to create a folder for better organization of your analysis, click .

Select SQL Page

6. Select the source of the SQL statements for Analysis: SQL Repository or SQL Scanner.

7. Select the SQL statement(s) to add to the Analysis from your predefined SQL statements, or you may add a statement to this Analysis by clicking .

8. Under the SQL Query Plans will be analyzed section, select the options for retrieving your query plan.

• Using existing query plan saved with the SQL
  This option uses the query plan that was saved with the SQL statement at the time that statement was saved to the SQL Repository, or scanned in the SQL Scanner.
• Obtaining a new query plan under the current connection
  This option retrieves the query plan with the current database logon. This current query plan is compared to the query plan that is retrieved after executing the configuration changes.

Configuration Page

9. Select to view the configuration parameters for the various options within Adaptive Server.

10. Make any parameter changes by inserting a value in the New Value column.

11. Click Finish to perform the analysis.

Reviewing Configuration Analyzer Results

12. After the analysis, you can see the overall results by clicking the Analyzer, and its related information, in the tree structure in the left pane and viewing the corresponding information in the panes to the right.

13. In the right pane for the Scenarios, click Prognosis to see overall performance changes and SQL details.

Related Topic

Configuration_Analyzer_Overview

Migration Analyzer Tutorial

The Migration Analyzer helps you to preempt performance degradation when performing database migrations, upgrades, and application rollouts. It ensures reliable database performance by tracking query plan and cost changes. You have an option to integrate abstract plan management to help stabilize SQL performance during migrations.

1. Click .

2. Click . If this is your first time in the Migration Analyzer, the New Analysis wizard automatically opens.

Analyzer Page

3. In the New Analysis wizard, under the Analyzer page, specify if you want to check the effects of database migration by either Creating a new Analyzer or Continuing an existing Analyzer.

4. Give your analysis a name and description for easy reference.

5. If you want to create a folder for better organization of your analysis, click .

Select SQL Tab

6. Select the source of the SQL statements for Analysis: SQL Repository or SQL Scanner.

7. Select the SQL statement(s) to add to the Analysis from your predefined SQL statements, or you may add a statement to this Analysis by clicking .

8. Under the SQL Query Plans will be analyzed section, select the options for retrieving your query plan.

   • Using existing query plan saved with the SQL
     This option uses the query plan that was saved with the SQL statement at the time that statement was saved to the SQL Repository, or scanned in the SQL Scanner.
   • Obtaining a new query plan under the current connection
     This option retrieves the query plan with the current database logon that is assumed to be the source Adaptive Server instance you are migrating from. This current query plan is compared to the query plan that is retrieved from the destination Adaptive Server instance for the migration.

Migration Page

9. Give a name and description to your Analysis Scenario (i.e. "Migration to ASE 15.0").

10. Specify the connection information of the destination Adaptive Server instance including login name, password, and server name. If you want to test the specified connection, click .

11. Select whether to use the default database and user or whether you want to login with different database and user information.

Destination Configuration Page

The Destination Configuration page is used to change the database parameters on the migration database and only available after you have entered your migrating database connection information on the Migration page.

12. You can switch the list of configuration parameters for Adaptive Server available in the parameter grid by selecting the parameter category from the Show configuration parameters for drop-down list

13. Make any parameter changes by inserting a value in the New Value column.

14. Click OK to perform the analysis.

Reviewing Migration Analyzer Results

15. After the analysis, you can see the overall results by clicking the Analyzer, and its related information, in the tree structure in the left pane and viewing the corresponding information in the panes to the right.

16. In the right pane for the scenario, click Prognosis to see overall performance changes and SQL details.

Related Topic

Migration Analyzer Overview

Index Impact Analyzer Tutorial

Index Impact Analyzer allows you to analyze the impact of new indexes on other SQL statements in your database.

Note: The indexes that are used in these scenarios will be physically created in order to retrieve the query plans for analysis and then dropped.

1. Click .

2. Click New Analysis. If this is your first time in the Index Impact Analyzer, the New Analysis wizard automatically opens.

Analyzer Page

3. In the New Analysis wizard, under the Analyzer page, select to check the effects of index creation by either Creating a new Analyzer ... or Continuing an existing Analyzer ....

4. Give the analysis a name and description for easy reference.

5. If you would like to create a folder for better organization of your analysis, click .

Select SQL Page

6. Choose the source of the SQL statements for Analysis: SQL Repository or SQL Scanner.

7. Select the SQL statement(s) to add to the Analysis from your predefined SQL statements, or you may add a statement to this Analysis by selecting .

8. Under the SQL Query Plans will be analyzed section, select the options for retrieving your query plan.

• Using existing query plan saved with the SQL
  This option uses the query plan that was saved with the SQL statement at the time that statement was saved to the SQL Repository, or scanned in the SQL Scanner.

• Obtaining a new query plan under the current connection
  This option retrieves the query plan with the current database logon. This current query plan is compared to the query plan that is retrieved after creating the new index(es).

Index Page

9. Name the Index Scenario and enter an optional description for easy reference.

10. Selecting the segment for the index scenario to be created in and the number of consumers.

11. If the Index Impact Analysis was called from the Index Advisor, the index scenarios are automatically display and you can choose the index scenarios to include for analysis.

12. To manually add indexes click .

• Give the index a name for easy reference.

• In the Index based on section of this page, specify in which database to create this index and specify the owner to use, and then specify the table that contains the column(s) to include in the index.

• The columns of the table display in the Table columns page and can be selected to use in the index scenario, under the Index columns page, by double clicking them or using the right arrow button.

• Specify the Index type by checking the option to have the index Unique and whether to create the index as Clustered or NonClustered.

13. Click OK to perform the Index Impact Analysis.

Note: The indexes are physically created in the database, and then the query plans for the selected SQL statements are retrieved. After the query plan retrieval, the indexes are dropped.

Review Index Impact Analysis Results

14. After the analysis, you can see the overall results by clicking the Analyzer, and its related information, in the tree structure in the left pane and viewing the corresponding information in the panes to the right.

15. In the right pane for the scenarios, click Prognosis to see overall performance changes and SQL details.

Related Topic

Index Impact Analyzer Overview

Index Usage Analyzer Tutorial

The Index Usage Analyzer identifies unused indexes by analyzing query plans from SQL statements in your database applications. It examines their query plans and reports any indexes in the database that are not used. You can use this module to quickly identify the indexes in databases that are not contributing to the performance of the database applications These unused indexes can then be deleted to free up space and improve the speed of the database applications and maintenance.

1. Click .

2. Click . If this is your first time in the Index Usage Analyzer, the New Analysis wizard automatically opens.

Analyzer Page

3. Give the analysis a name and description for easy reference.

4. For better organization, create a folder for the analysis you are creating by clicking .

Select SQL Page

5. Select the source of the SQL statements for Analysis: SQL Repository or SQL Scanner.

6. Select the SQL statement(s) to add to the Analysis from your predefined SQL statements or you may add a statement to this Analysis by selecting .

7. Click OK.

Review Unused Index Analyzer Results

8. After the analysis, you can see the overall results by clicking the Analyzer, and its related information, in the tree structure in the left pane and viewing the corresponding information in the panes to the right.

9. In the right pane, click Index Summary to see a list of indexes used and unused.

Related Topic

Index Usage Analyzer Overview