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
- Select the Optimize SQL tab in the main window.
-
Select the Optimize for Cursor checkbox under the SQL Text window in SQL Optimizer.
-
Click Settings.
-
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.
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:
-
When SQL Optimizer generates virtual index alternatives, it also creates virtual execution plans for each index set. You can create temporary indexes on your database to retrieve the actual execution plans for the index sets. See Fill Missing Estimated Plans for more information.
- Once you retrieve the execution plans, you can use the plan cost to help you select alternatives to test with your database environment. See Compare Plan Cost for more information.
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.
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:
-
When SQL Optimizer generates virtual index alternatives, it also creates virtual execution plans for each index set. You can create temporary indexes on your database to retrieve the actual execution plans for the index sets. See Fill Missing Estimated Plans for more information.
- Once you retrieve the execution plans, you can use the plan cost to help you select alternatives to test with your database environment. See Compare Plan Cost for more information.
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.
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
- Select the Optimize SQL tab in the main window.
- Select a SQL Rewrite session.
- Enter the SQL statement in the Alternative Details pane.
-
Click
.
Tip: Click the arrow besides
-
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.