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.
Select the Optimize SQL tab in the main window.
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.
Click . The Select Connection window displays.
Select a connection. Review the following for additional information:
Select Connection | Description |
Connection |
Click Tip: Click |
Select Database and User | Description |
Database |
Click |
Set User |
Click |
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:
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.
Click after SQL Optimizer completes the SQL rewrite process to compare your original SQL statement with the alternatives generated.
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
Click the drop-down arrow beside and select Test Run All.
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.
Select the Optimize SQL tab in the main window.
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.
Click . The Select Connection window displays.
Select a connection. Review the following for additional information:
Select Connection | Description |
Connection |
Click Tip: Click |
Select Database and User | Description |
Database |
Click |
Set User |
Click |
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:
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.
Click after SQL Optimizer completes the SQL rewrite process to compare your original SQL statement with the alternatives generated.
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
Click the drop-down arrow beside and select Test Run All.
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.
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.
Enter a SQL statement in the Original SQL pane.
Click to generate alternative execution plans for your SQL statement.
Tip: Click to abort the process.
Select a connection. Review the following for additional information:
Select Connection | Description |
Connection |
Click Tip: Click |
Select Database and User | Description |
Database |
Click |
Set User |
Click |
Review the run time statistics in the Plans pane to identify the best alternative.
Click .
Review the following for additional information:
Deploy | Description |
Select a plan to deploy |
Click |
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 |
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
Select the Scan SQL tab in the main window.
Click Add Scanner Job. The Add Scanner Jobs window displays.
Select a connection to use. Review the following for additional information:
Select Connection | Description |
Connection |
Click Tip: Click |
Select Database and User | Description |
Database |
Click |
Set User |
Click |
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 Tip: Click |
SQL Profiler Page | Description |
Available trace files/table |
Click |
Database |
Click |
Set User |
Click |
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 |
Add by directory |
Click 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. |
Click Finish to start SQL Scanner.
Select Task from the Scanner Explorer.
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.
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.
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.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Nutzungsbedingungen Datenschutz Cookie Preference Center