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 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. |
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 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. |
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 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. |
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 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. |
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 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. |
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. |
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. 利用規約 プライバシー Cookie Preference Center