SQL Optimizer for SQL Server 10.1.2 - User Guide

Tutorials

Using SQL Rewrite mode in Optimize SQL consists of two steps. In the first step, SQL Optimizer generates semantically equivalent alternatives with unique execution plans for your original SQL statement. A SQL Server cost estimate displays for each alternative generated. In the second step, SQL Optimizer executes the alternatives to benchmark each statement's performance. This provides execution times and run time statistics that allow you to find the best SQL statement for your database environment.

Tip: SQL Server cost only provides an estimate of resource usage to execute a SQL statement. Since statements with higher cost may perform better, you should test alternatives generated to determine the best statements for your database environment.

Step 1: Optimize the SQL Statement

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

2. Select SQL Rewrite from the Optimize SQL start page.

   Note: If the start page does not display, click the arrow beside and select New SQL Rewrite Session.

3. Enter a SQL statement in the Alternative Details pane.

4. Click . The Select Connection window displays.

5. Select a connection. 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. Optimize SQL to use in a cursor. SQL Server generates different execution plans for SQL statements embedded in a cursor declaration. This needs to be considered when using SQL Optimizer to retrieve execution plans, retrieve run times, and generate SQL alternatives. Specific cursor settings need to be selected before SQL statements that come from or will be embedded in a cursor declaration. These settings tell SQL Optimizer to use cursor simulation when retrieving execution plans and run time information.

   Complete the following steps to select cursor settings:

   1. Select the Optimize for Cursor checkbox.
   2. Click Settings.
   3. Select the cursor type and the associated cursor argument.

7. Click to optimize the SQL statement or click to optimize the SQL statement and generate index alternatives in one step.

   You can configure hints and other optimization settings, such as temporary table generation and ANSI JOIN syntax, in the Options pages before performing this step. The Intelligence Level that determines the number of SQL Server hints applied and the number of alternatives generated can also be configured from the Options pages.

   Note: You can create a temporary table for your SQL statement. See Tutorial: User-Defined Temp Tables for more information.

8. Click after SQL Optimizer completes the SQL rewrite process to compare your original SQL statement with the alternatives generated.

Step 2: Benchmark Alternative SQL Statements

The Test Run function provides an efficient way to benchmark alternatives generated by SQL Optimizer. You can execute selected alternatives to obtain actual execution statistics. This function does not affect network traffic since SQL Optimizer can provide these statistics without having to retrieve result sets from the database server. Additionally, data consistency is maintained when using SELECT, SELECT INTO, INSERT, DELETE, and UPDATE statements because these statements are run in a transaction that is rolled back after execution.

To benchmark a SQL statement alternative

1. Click the drop-down arrow beside and select Test Run All.

2. The Test Run Settings dialog opens. Select test run options to apply to this test run.
   • To allow SQL Optimizer to determine the best test run settings, answer the questions in the Test Run Settings dialog and click Start Test Run.
   • To customize test run settings, click Customize Test Run Settings at the bottom of the page and specify options for this test run.
3. The execution statistics display in the Alternatives pane once the test run is finished. Select an alternative to see more information in the Alternative Details and Execution Plan panes.

Related Topics

About SQL Optimization

Tutorial: Optimize SQL (SQL Rewrite)

Using SQL Rewrite mode in Optimize SQL consists of two steps. In the first step, SQL Optimizer generates semantically equivalent alternatives with unique execution plans for your original SQL statement. A SQL Server cost estimate displays for each alternative generated. In the second step, SQL Optimizer executes the alternatives to benchmark each statement's performance. This provides execution times and run time statistics that allow you to find the best SQL statement for your database environment.

Tip: SQL Server cost only provides an estimate of resource usage to execute a SQL statement. Since statements with higher cost may perform better, you should test alternatives generated to determine the best statements for your database environment.

Step 1: Optimize the SQL Statement

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

2. Select SQL Rewrite from the Optimize SQL start page.

   Note: If the start page does not display, click the arrow beside and select New SQL Rewrite Session.

3. Enter a SQL statement in the Alternative Details pane.

4. Click . The Select Connection window displays.

5. Select a connection. 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. Optimize SQL to use in a cursor. SQL Server generates different execution plans for SQL statements embedded in a cursor declaration. This needs to be considered when using SQL Optimizer to retrieve execution plans, retrieve run times, and generate SQL alternatives. Specific cursor settings need to be selected before SQL statements that come from or will be embedded in a cursor declaration. These settings tell SQL Optimizer to use cursor simulation when retrieving execution plans and run time information.

   Complete the following steps to select cursor settings:

   1. Select the Optimize for Cursor checkbox.
   2. Click Settings.
   3. Select the cursor type and the associated cursor argument.

7. Click to optimize the SQL statement or click to optimize the SQL statement and generate index alternatives in one step.

   You can configure hints and other optimization settings, such as temporary table generation and ANSI JOIN syntax, in the Options pages before performing this step. The Intelligence Level that determines the number of SQL Server hints applied and the number of alternatives generated can also be configured from the Options pages.

   Note: You can create a temporary table for your SQL statement. See Tutorial: User-Defined Temp Tables for more information.

8. Click after SQL Optimizer completes the SQL rewrite process to compare your original SQL statement with the alternatives generated.

Step 2: Benchmark Alternative SQL Statements

The Test Run function provides an efficient way to benchmark alternatives generated by SQL Optimizer. You can execute selected alternatives to obtain actual execution statistics. This function does not affect network traffic since SQL Optimizer can provide these statistics without having to retrieve result sets from the database server. Additionally, data consistency is maintained when using SELECT, SELECT INTO, INSERT, DELETE, and UPDATE statements because these statements are run in a transaction that is rolled back after execution.

To benchmark a SQL statement alternative

1. Click the drop-down arrow beside and select Test Run All.

2. The Test Run Settings dialog opens. Select test run options to apply to this test run.
   • To allow SQL Optimizer to determine the best test run settings, answer the questions in the Test Run Settings dialog and click Start Test Run.
   • To customize test run settings, click Customize Test Run Settings at the bottom of the page and specify options for this test run.
3. The execution statistics display in the Alternatives pane once the test run is finished. Select an alternative to see more information in the Alternative Details and Execution Plan panes.

Related Topics

About SQL Optimization

Tutorial: Optimize SQL (Plan Control)

Using Plan Control mode in Optimize SQL consists of two steps. In the first step, SQL Optimizer generates execution plan alternatives for your SQL statement without changing the source code. You can then execute the alternatives to retrieve run time statistics and identify the best alternative for your database environment. In the second step, you can use Plan Control mode to deploy the execution plan to the Manage Plan Guides module as an SQL Server plan guide.

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

Step 1: Generate and Execute Execution Plan Alternatives

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

2. Select Plan Control from the Optimize SQL start page.

   Note: If the start page does not display, click the arrow beside and select New Plan Control Session.

3. Enter a SQL statement in the Original SQL pane.

4. Click to generate alternative execution plans for your SQL statement.

   Tip: Click to abort the process.

5. Select a connection. 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 execute all alternative execution plans to retrieve run time statistics.

7. Review the run time statistics in the Plans pane to identify the best alternative.

Step 2: Deploy Execution Plan as a Plan Guide

1. Click .

2. Review the following for additional information:

   Deploy	Description
   Select a plan to deploy	Click and select an execution plan alternative to deploy.
   Original SQL	Displays the SQL used to deploy the plan guide.
   Plan Guide	Allows you to modify the plan guide.
   Deploy	Displays the SQL Server script SQL Optimizer uses to create the plan guide. Click to make a copy of the script or to create the plan guide.

Tutorial: SQL Scanner

SQL Scanner extracts SQL statements embedded in database objects, captured from SQL Profiler, or stored in application source code and binary files. SQL Scanner retrieves and analyzes execution plans for extracted SQL statements from SQL Server to identify statements with performance bottlenecks. You can copy statements classified as problematic (first priority) or complex (second priority) into SQL Optimizer for analysis.

To scan SQL

1. Select the Scan SQL tab in the main window.

2. Select Welcome from the Scanner Explorer.

3. Click Add Scanner Job. The Add Scanner Jobs window displays.

4. 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.

5. Select the page for the item you want to scan in the Add Scanner Jobs pane. Review the following for additional information:

   Database Objects Page	Description
   Database Objects	Select a database object and click to add the object to scan. Tip: Click to browse for database objects to add.
   SQL Profiler Page	Description
   Available trace files/table	Click to add SQL Server Profiler trace files or trace tables to scan.
   Database	Click to select the database of the SQL to scan.
   Set User	Click to select your user name.
   Source Code Page	Description
   Source code type	Select Text/Binary files or COBOL programming source code to indicate the source code type.
   Add by file	Click and browse to the files you want to scan.
   Add by directory	Click and browse to the directories you want to scan. Note: Select the Include Sub-directory checkbox to scan sub-directories.
   Connection for scanning	Select the database and user name settings.
   Note: SQL Scanner helps you manage scan jobs by organizing them into groups. Use the Group Information page to create a new group or to add the current scan job to an existing group.

6. Click Finish to start SQL Scanner.

7. Select Task from the Scanner Explorer.

8. Select Scanner in the Task pane to view information about your scan jobs.

   The Group Summary pane sorts information about your scan jobs by groups. Additional information displays in the Type of Jobs and Job Status panes.

9. Select a group from the scanner node to see details for the group in the Job List pane.

   Details displayed in the Job List pane include the number of SQL statements found and the classification for each statement. Additional information displays in the SQL Classification and Job Detail Information panes.

10. Select a scan job from the group node to see details for the scan job.

    The SQL List pane displays SQL statements identified by classification. Selecting a SQL statement in the SQL List pane displays information about the statement in the SQL Text and Execution Plan panes.