지금 지원 담당자와 채팅
지원 담당자와 채팅

SQL Optimizer for SQL Server 10.1.2 - User Guide

Welcome to SQL Optimizer Optimize SQL
Create Optimize SQL Sessions Open Optimize SQL Sessions Rewrite SQL Plan Control Use Temporary Tables
Optimize Indexes Find SQL Scan SQL Manage Plan Guides Configure Options Tutorials About Us

About Optimizing SQL (SQL Rewrite)

Use SQL Rewrite sessions to optimize SQL statements by using SQL Optimizer's Artificial Intelligence engine to rewrite SQL statement syntax. This process begins with SQL Optimizer analyzing your original SQL statement and generating a list of semantically equivalent statement alternatives. The SQL rewrite process continues until SQL Optimizer cannot generate additional alternatives. SQL Optimizer then applies a set of SQL Server hints to each alternative to generate additional alternatives.

Once SQL Optimizer completes the SQL rewrite process, you can execute the alternatives to retrieve execution plans and run time statistics. You can compare execution times and run time statistics for alternatives to identify the best statements for your database environment.

Notes:

  • Although different SQL statement alternatives produce the same results, SQL Server may use different paths to retrieve data for each alternative. The database structure, indexes, and data volumes affect execution statistics. Therefore, you should test the alternatives with your database environment to find the best alternative.
  • The Intelligence Level setting you select affects the duration of the optimization process and the number of alternatives SQL Optimizer generates.

  

Related Topics

About Optimizing SQL (Plan Control)

Select Connection

To select a connection

  1. Select the Optimize SQL tab in the main window.
  2. Click

  3. Review the following for additional information:

    Select Connection Description

    Connection

    Click to select a previously created connection.

    Tip: Click to open the Connection Manager to create a new connection. See Connect to SQL Server for more information.

    Select Database and User Description

    Database

    Click to select the database to match your SQL statement.

    Set User

    Click to select your user name.

  

Related Topics  

Optimize SQL Statements

In a SQL Rewrite session, SQL Optimizer generates semantically equivalent alternatives with unique execution plans for your original SQL statement.

After SQL Optimizer generates SQL alternatives, you can test run the alternatives to benchmark their performance. SQL Optimizer provides execution times and run-time statistics for each alternative to help you identify the best SQL statement for your database environment.

To optimize a SQL statement

  1. Select the Optimize SQL tab in the main window.
  2. Select SQL Rewrite from the Optimize SQL start page.
  3. Enter a SQL statement in the Alternative Details pane.

  4. Click . The Select Connection window displays.

  5. Select a connection to use. Review the following for additional information:

    Select Connection Description

    Connection

    Click to select a previously created connection.

    Tip: Click to open the Connection Manager to create a new connection. See Connect to SQL Server for more information.

    Select Database and User Description

    Database

    Click to select the database to match your SQL statement.

    Set User

    Click to select your user name.
  6. Click to optimize the SQL statement or click to optimize the SQL statement and generate index alternatives in one step.

  7. After SQL Optimizer finishes generating alternatives, you can test run the alternatives. See Test Run SQL Alternatives for more information.
  8. If you selected multiple alternatives to test run, the Test Run Settings dialog opens. Select test run criteria to apply to this test run. See Test Run Settings.
  9. After the test run process is finished, the execution statistics display in the Alternatives pane. Select an alternative to view details in the Alternative Details pane and the Execution Plan pane.
  10. Select the Compare tab to compare SQL alternatives to the original SQL.

To clear the SQL Rewrite window

To clear the results, right-click inside the Alternative Details pane and do one of the following:

  • Select Clear Optimization Results | Keep Original Scenario to clear only the results and retain both the original SQL statement and the database connection.
  • Select Clear Optimization Results | Clear Original Scenario to clear the SQL Rewrite window, but retain the database connection.

  

Related Topics

Auto Optimize SQL Statements

Test Run SQL Alternatives

Test Run SQL Alternatives

After SQL Optimizer generates SQL alternatives, you can test run the alternatives. Test run statement alternatives to view their execution statistics. You can test run a single statement, a selected group of statements, or all statements simultaneously.

Note: SQL Optimizer test runs the statements against the database during this process to obtain accurate run-time statistics. SQL Optimizer automatically rolls back any changes made to the database after it executes SELECT, SELECT INTO, INSERT, DELETE, or UPDATE statements.

To test run SQL alternatives

  1. After generating SQL alternatives in a SQL Rewrite session (see Optimize SQL Statements), use one of the following methods to test run the SQL alternatives.
    • To test run a single SQL alternative, select the alternative and click .
    • To test run a single SQL alternative and specify test run criteria, select the alternative and click the arrow beside and select Test Run Special - Current.
    • To test run multiple alternatives simultaneously, select the alternatives and click the arrow beside and select Test Run Selected.
    • To test run all alternatives simultaneously, click the arrow beside and select Test Run All.
  2. If you selected to test run a single alternative using special test run criteria, the Test Run Special Settings dialog opens. Specify criteria for this test run only. See Test Run Special Settings for more information.
  3. If you selected to test run multiple or all alternatives simultaneously, the Test Run Settings dialog opens. Specify test run options to apply to this test run only. See Test Run Settings for more information.
  4. After the test run process is finished, the execution statistics display in the Alternatives pane. Select an alternative to view details in the Alternative Details pane and the Execution Plan pane. See View Execution Statistics (SQL Rewrite) for more information.
  5. Select the Compare tab to compare SQL alternatives to the original SQL.

Notes:

  • Click to stop the test run process for multiple alternatives.
  • To stop the process for a single alternative, select the running plan alternative and click .

To clear the SQL Rewrite window

To clear the results, right-click inside the Alternative Details pane and do one of the following:

  • Select Clear Optimization Results | Keep Original Scenario to clear only the results and retain both the original SQL statement and the database connection.
  • Select Clear Optimization Results | Clear Original Scenario to clear the SQL Rewrite window, but retain the database connection.

 

Related Topics

Optimize SQL Statements

Auto Optimize SQL Statements

관련 문서

The document was helpful.

평가 결과 선택

I easily found the information I needed.

평가 결과 선택