Chat now with support
Chat with Support

SQL Optimizer for SQL Server 10.1.2 - User Guide

Welcome to SQL Optimizer Optimize SQL
Create Optimize SQL Sessions Open Optimize SQL Sessions Rewrite SQL Plan Control Use Temporary Tables
Optimize Indexes Find SQL Scan SQL Manage Plan Guides Configure Options Tutorials About Us

SQL Classification Categories

SQL Optimizer classifies the SQL statements extracted in Scan SQL and in Find SQL, as well as the SQL you enter in Optimize SQL. The SQL are classified into one of the following categories: Simple, Complex, Problematic, or Invalid.

For a better understanding of each of these categories, review the following category descriptions.

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 for more information.

Note: You can specify SQL classification rules in the Options dialog. See SQL Classification Options for more information.

 

  

Find SQL from SQL Trace

Find SQL helps you locate the most resource-intensive SQL in your server. You can use Find SQL to inspect SQL captured by Plan Cache and SQL Profiler.

Find SQL from Plan Cache evaluates existing SQL performance to find the most resource-intensive SQL statements. Resource usage such as CPU Time and Elapsed Time are evaluated. You can view the top consuming SQL statements from different areas, such as batches or database objects.

Find SQL from SQL Trace allows you to extract SQL statements and their performance statistics from trace files or trace tables collected by SQL Profiler. You can identify the most resource-intensive SQL statements in your traces.

Additionally, you can:

  • Review execution statistics and query plans of the SQL
  • Check resource consumption at the SQL Batch and Database Object levels
  • Optimize the performance of your SQL by sending them to Optimize SQL or Optimize Indexes
  • Save a SQL and its XML plan for others to review

 

Related Topics

Find SQL - the Homepage

 

Start finding SQL from SQL Trace

You can extract SQL statements and their performance statistics from trace files or trace tables collected by SQL Profile to identify the most resource-intensive SQL statements in your traces.

For more information about trace templates, see SQL Trace Templates.

To create a new SQL Trace search session

  1. Select Find SQL.

    Note: If you are creating your first search session, see Find SQL - the Homepage.

  2. To conduct a search on a server for which you have previously conducted a search, select the server from the list. Or click Show all servers to view entire list and select the server.
    • Then click Start a new SQL Trace search.
    • Or select Click to start finding SQL in SQL Trace if you have never conducted a SQL Trace search on this server.
  3. To conduct a search on a new server, click Find SQL in another server.
    1. Select a connection. See Select Connection.
    2. Select Click to start finding SQL in SQL Trace in the Find SQL from SQL Trace panel that displays.

  4. The Add SQL Trace Search dialog opens. Select a trace file or table. Review the following for more information:

    Notes:

    • You can extract SQL statements and their performance statistics from trace files or trace tables.
    • You can use trace templates provided by SQL Optimizer or use your own templates.

    Add from Trace Files

    		 Click to add SQL from trace files.

    Add from Trace Tables

    Click to add SQL from trace tables. The Add Trace Table dialog opens.

    Select a connection.

    • Connection—Select the connection where the trace table is located.
    • Database—Specify the database where the trace table is located.
    • Schema—Specifies the owner of the trace table.

    Then select a trace table.

    Note: Find SQL requires certain event columns in your trace in order to analyze it. For convenience, SQL Optimizer provides trace templates for you to use. These template files are located in the Find SQL - Trace Templates folder in the SQL Optimizer installation directory.

    See SQL Trace Templates for more information.

    Show Collect Criteria

    Click to filter the SQL to collect. You can filter by:

    • Application Name
    • Database Name
    • Login Name
    • Host Name
  5. Click OK. The session name is added to the list of saved sessions and the search process starts.

  6. A search progress bar displays next to the search name during the search process. The following information and options are displayed in the Find SQL from SQL Trace pane.

    Start a new search

    Select to begin a new search and to display the Add SQL Trace Search dialog box.

    Search name

    Name you have given to your search.

    Click to open the dashboard-style Search Result page for this search.

    Notes:

    • The default format is the current date and time.
    • Right-click the name to rename or delete the search.
    Status

    Displays status of search:

    • Collecting

      Note: Click to abort search.

    • Complete

    Show all SQL Trace searches in this server

    Select to display all the searches you have conducted on the selected server.

    Note: Right-click the search to delete the selected search.

  7. When the search is finished, a dashboard-style Search Results page displays. See Search Results (SQL Trace) for more information.

    Click a pie chart to view the top-consuming SQL for that resource. Each pie chart represents a different view of the most-expensive (top-consuming) SQL. In each view, the SQL statements are filtered by a different resource consumed and by a different SQL location.

  8. After viewing top-consuming SQL on a Summary Graph page, to return to the dashboard click or click the Search session name in the breadcrumb.
  9. For more information about the different views, see the following:

SQL Trace Templates

Find SQL requires certain event columns in your trace in order to analyze it. For your convenience, SQL Optimizer provides trace template files (.tdf) for you to use. The template files are located in the Find SQL - Trace Templates folder in the SQL Optimizer installation directory.

If you create your own template, make sure it defines the events and columns listed in the following table.

Methods for setting up SQL Trace Description

Use trace templates provided by SQL Optimizer

 Use trace templates provided by SQL Optimizer to collect SQL. You can locate the templates in the installation directory.

Use your own templates

Ensure that the following trace events and columns are captured.

Events Columns
RPC: Completed EventClass
RPC: Starting TextData
SP:Completed CPU
SP:Starting Writes
SP:StmtCompleted Reads
SQL:BatchCompleted Duration
SQL:BatchStarting SPID
SQL:StmtCompleted DatabaseName
EventSequence
LineNumber
ObjectID
ObjectName
Offset
SourceDatabaseID

  

Related Topics

Find SQL - the Homepage

Top SQL Statements Page

Top Batches Page

Top Database Objects Page

Select Connection for SQL Trace

Note: This topic may not include a description for every field in the screen or dialog.

To select a connection

  1. Select Find SQL.

  2. Select Click here to start Find SQL.

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

    Password

    		 Password
  4. Click OK.

To select a connection when using a trace table

A separate connection window displays when you add trace tables.

  1. Select Add from Trace Tables.

  2. Review the following for additional information:

    Add Trace Tables Description

    Connection

    Specify the connection where the trace table is located.

    Database

    		 Specify the database where the trace table is located.

    Schema

    		 Specifies the owner of the trace table.
  3. Click OK.

  

Related Topics  

About Finding SQL

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating