SQL Optimizer for SQL Server 10.1 - User Guide

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.

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

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

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

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

2. Create a scan job using the Add Scanner Jobs wizard. See "Scan SQL" in the online help for more information.

3. Select a group in the Scanner node in the Task pane.

4. 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. See "Use Temporary Tables in Scan SQL" in the online help for more information.
   Note: You can specify SQL classification rules in the Options dialog. See "SQL Classification Options" in the online help for more information.

To view SQL classification for individual statements

1. Select a group in the Scanner node in the Task pane.

2. Select a scan job from the Group node.

3. 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.
4. Select a SQL statement in the list to view SQL text.