Chat now with support
Chat with Support

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.

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. See "About Scanning SQL" in the online help for more information.

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. See "Optimize SQL Statements" in the online help for more information.

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. See "Execute SQL Alternatives" in the online help for more information.

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. See "Compare SQL Statements" in the online help for more information.

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

  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:

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

  7. Click Finish to start SQL Scanner.

  8. Select Task from the Scanner Explorer.

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

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

  11. 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.
Self Service Tools
Knowledge Base
Notifications & Alerts
Product Support
Software Downloads
Technical Documentation
User Forums
Video Tutorials
RSS Feed
Contact Us
Licensing Assistance
Technical Support
View All
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating