Introduction
SQL Optimizer for SQL Server® uses the following SQL optimization workflow to help you ensure that your SQL statements perform optimally in your database environment.
| Procedure | Description |
|---|---|
| Identify Problematic SQL Statements |
The SQL Scanner locates and extracts problematic SQL statements directly from your source code without execution. SQL Scanner analyzes the operations of the execution plan and identifies potential performance bottlenecks. Note: You need to use Microsoft® SQL Server Profiler to capture dynamic SQL statements. Save the captured dynamic SQL statements into a trace file or trace table and use SQL Scanner to extract the statements. |
| Optimize SQL Statements |
The SQL Optimizer uses artificial intelligence to generate alternatives with unique execution plans for problematic SQL statements. SQL Optimizer generates the alternatives by analyzing SQL statement syntax and database structure. You can also use hints during the optimization process. |
| Test Run SQL Alternatives |
The Test Run function tests the performance of each alternative SQL Optimizer generates. This provides execution times for each alternative to determine the best statement for your database environment. |
| Compare SQL Alternatives |
The Compare feature displays details for two SQL statements side-by-side. You can compare an alternative to the original SQL statement or compare two alternatives. |
| Generate Reports |
The Report feature creates a SQL resolution report after you complete the optimization process. See "Generate Optimize SQL Resolution Report" in the online help for more information. |
Identify Problematic SQL
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
-
Select the Scan SQL tab in the main window.
- Select Welcome from the Scanner Explorer.
-
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.
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:
-
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.
| Database Objects Page | Description |
|
Database Objects |
Select a database object and click Tip: Click |
| 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 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. | |
View SQL Classification
SQL Scanner extracts syntactically correct SQL statements and retrieves their execution plans. SQL Scanner then classifies the extracted statements as Simple, Complex, Problematic, or Invalid. You can view the classification of extracted statements by scan jobs or individually.
To view SQL classification for a scan job
- Select the Scan SQL tab in the main window.
-
Create a scan job using the Add Scanner Jobs wizard.
-
Select a group in the Scanner node in the Task pane.
-
Select a scan job in the Job List pane to view classification information in the SQL Classification pane. Review the following for additional information:
Classification Description Simple SQL statements are classified as simple when the number of tables referenced in the execution plan is less than the lower limit of the complex table scan operations range. Complex SQL statements are classified as complex when the number of tables referenced in the execution plan exceeds the lower limit of the complex table scan operations range. Problematic SQL statements are classified as problematic when SQL Optimizer determines they can be optimized. Problematic SQL statements satisfy one or more of the following criteria:
- The number of tables referenced in the execution plan exceeds the upper limit of the complex table scan operations range.
- A full table scan executed in a nested loop exceeds the user-defined threshold table size.
- A single table scan executed exceeds the user-defined threshold table size.
Invalid SQL statements are classified as invalid for one of the following reasons:
- The current user does not have privileges to use the tables, views, or other database objects referenced in the SQL statement.
- The database and/or user do not match the database and user (object owner) for the SQL statement.
- The SQL statement is dynamically constructed but is not a Single Command Line Dynamic (SCLD) SQL statement. SQL Optimizer can only identify SCLD SQL statements.
- In Scan SQL, the SQL statement uses a temporary table that was not created before starting the Scan SQL session.
Note: You can specify SQL classification rules in the Options dialog.
To view SQL classification for individual statements
-
Select a group in the Scanner node in the Task pane.
-
Select a scan job from the Group node.
-
Review the list of SQL in the SQL List pane.
Tips:
- Click
to group the SQL by classification category.
- Click
to list the SQL numerically.
- Click
- Select a SQL statement in the list to view SQL text.