Tchater maintenant avec le support
Tchattez avec un ingénieur du 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.

 

  

Use Execution Plans

The execution plan displays the steps a database takes to execute a SQL statement. You can use the execution plan to determine if a statement is efficient.

The following displays a sample execution plan in tree plan format:

Each step of the tree indicates how SQL Optimizer retrieves rows of data. The first line of the execution plan displays the SQL statement type, such as SELECT. The remaining lines represent an operation. The operations are numbered in the order of execution to make the plan easier to read.

SQL Optimizer executes each child operation before the parent operation. For some SQL statements, SQL Optimizer executes the parent operation once it retrieves a single row from the child operation. Other SQL statements require that SQL Optimizer retrieve all rows from the child operation before it executes the parent operation.

The annotated execution plan includes the following information for each step:

  • Execution order number

  • Join syntax (annotated)

  • Filter syntax (annotated)

  • Object name

  • Table access

  • Index scan

  • Cost

  • Partition name

About Execution Plans (Find SQL)

The execution plan displays the steps a database takes to execute a SQL statement. You can use the execution plan to determine if a statement is efficient.

The following displays a sample execution plan in tree plan format:

Each step of the tree indicates how SQL Optimizer retrieves rows of data. The first line of the execution plan displays the SQL statement type, such as SELECT. The remaining lines represent an operation. The operations are numbered in the order of execution to make the plan easier to read.

SQL Optimizer executes each child operation before the parent operation. For some SQL statements, SQL Optimizer executes the parent operation once it retrieves a single row from the child operation. Other SQL statements require that SQL Optimizer retrieve all rows from the child operation before it executes the parent operation.

The annotated execution plan includes the following information for each step:

  • Execution order number

  • Join syntax (annotated)

  • Filter syntax (annotated)

  • Object name

  • Table access

  • Index scan

  • Cost

  • Partition name

Animate Execution Plans

You can animate each step in the execution plan in the order SQL Optimizer executes them.

To animate the execution plan or cancel the animation

» Right-click the execution plan and select Animate Plan Steps.

 

Related Topics

Copy Execution Plans

Fill Missing Execution Plans

 

Documents connexes

The document was helpful.

Sélectionner une évaluation

I easily found the information I needed.

Sélectionner une évaluation