立即与支持人员聊天
与支持团队交流

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

Optimize SQL Used in Cursors

SQL Server generates different execution plans for SQL statements embedded in a cursor declaration. This needs to be considered when you use SQL Optimizer to retrieve execution plans, retrieve run times, and generate SQL alternatives. You need to specific cursor settings before you use SQL statements extracted from or embedded in a cursor declaration. These settings tell SQL Optimizer to use cursor simulation when it retrieves execution plans and run time information.

Tip: To optimize a SQL statement extracted from within a cursor declaration identified with SQL Scanner, copy the statement into the SQL Text window of a new session in SQL Optimizer.

To select cursor settings

  1. Select the Optimize SQL tab in the main window.
  2. Select the Optimize for Cursor checkbox under the SQL Text window in SQL Optimizer.

  3. Click Settings.

  4. Select the Cursor Type and the associated Cursor Argument. Review the following for additional information:

    Cursor Type Cursor Argument Description

    SQL-92 Syntax

     

    Specify that the cursor syntax is based on the SQL-92 standard.

    INSENSITIVE

    Defines a cursor to use a temporary copy of data.

    SCROLL

    Defines a cursor to enable all fetch options (FIRST, LAST, PRIOR, NEXT, RELATIVE, or ABSOLUTE).

    Transact-SQL Extended Syntax

     

    Specify that the cursor syntax uses a set of Transact-SQL extensions.

     

    FAST FORWARD

    Defines a cursor to FORWARD_ONLY and READ_ONLY.

    FORWARD ONLY

    Defines a cursor to scroll from the first to the last row.

    SCROLL

    Defines a cursor to enable all fetch options (FIRST, LAST, PRIOR, NEXT, RELATIVE, or ABSOLUTE).

    STATIC

    Defines a cursor to use a temporary copy of data.

    KEYSET

    Defines a cursor that specifies the fixed membership and order of rows when you open the cursor.

    DYNAMIC

    Defines a cursor that reflects all data changes as you scroll around the cursor.

    READ ONLY

    Defines a cursor to prevent modification of data.

    SCROLL LOCKS

    Defines a cursor that locks rows as they are read to ensure their availability for later modification. Updates or deletions made through the cursor are guaranteed to succeed.

    OPTIMISTIC

    Defines a cursor that does not guarantee success for update or deletion of rows made after being read into the cursor.

 

Related Topics

Optimize SQL Statements

Test Run SQL Alternatives

Generate Index Alternatives

SQL Optimizer analyzes the following in your original SQL statement and table references to generate index alternatives:

  • SQL statement syntax
  • Related tables and indexes
  • Search arguments
  • Table join conditions

Once SQL Optimizer generates alternatives, you can test them to evaluate improvements in database performance.

Note: SQL Optimizer does not physically create indexes on your database when generating alternatives.

Index Generation for SQL Server 2005 or later

When connected to Microsoft SQL Server 2005 or later, SQL Optimizer uses virtual indexes and Database Engine Tuning Advisor (DTA) information to provide index alternatives. SQL Optimizer generates virtual indexes and combines them into index sets. DTA information for the original SQL statement is then retrieved using each index set. If the DTA information indicates the indexes can be used by the SQL statement, the index set is saved. Index sets that cannot not be used are automatically eliminated. This ensures SQL Optimizer only provides index sets that impact performance.

Tips:

Index Generation for SQL Server 2000

When connected to Microsoft SQL Server 2000, SQL Optimizer provides you with individual index alternatives to evaluate, test or implement. Since SQL Server 2000 cannot create virtual indexes, DTA information is not used to eliminate alternatives that do not impact performance.

 

Related Topics

Generate Index Alternatives

About Generating Index Alternatives

SQL Optimizer analyzes the following in your original SQL statement and table references to generate index alternatives:

  • SQL statement syntax
  • Related tables and indexes
  • Search arguments
  • Table join conditions

Once SQL Optimizer generates alternatives, you can test them to evaluate improvements in database performance.

Note: SQL Optimizer does not physically create indexes on your database when generating alternatives.

Index Generation for SQL Server 2005 or later

When connected to Microsoft SQL Server 2005 or later, SQL Optimizer uses virtual indexes and Database Engine Tuning Advisor (DTA) information to provide index alternatives. SQL Optimizer generates virtual indexes and combines them into index sets. DTA information for the original SQL statement is then retrieved using each index set. If the DTA information indicates the indexes can be used by the SQL statement, the index set is saved. Index sets that cannot not be used are automatically eliminated. This ensures SQL Optimizer only provides index sets that impact performance.

Tips:

Index Generation for SQL Server 2000

When connected to Microsoft SQL Server 2000, SQL Optimizer provides you with individual index alternatives to evaluate, test or implement. Since SQL Server 2000 cannot create virtual indexes, DTA information is not used to eliminate alternatives that do not impact performance.

 

Related Topics

Generate Index Alternatives

Generate Index Alternatives

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

To generate index alternatives for a SQL statement

  1. Select the Optimize SQL tab in the main window.
  2. Select a SQL Rewrite session.
  3. Enter the SQL statement in the Alternative Details pane.
  4. Click .

    Tip: Click the arrow besides and select User Index to create a user defined virtual index based on your original SQL statement. See Create Virtual Indexes for more information.

  5. Select Index Details in the SQL Information pane to view index generation information.

    Tip: Click to stop the index generation process.

Note: When you generate virtual index alternatives, the execution plan is not retrieved for the SQL statement because SQL Optimizer does not physically create indexes on the database. See Fill Missing Estimated Plans for more information.

 

Related Topics

Test Run Index Alternatives

相关文档

The document was helpful.

选择评级

I easily found the information I needed.

选择评级