Test Run Index Alternatives
After SQL Optimizer generates index alternatives, you can test each alternative. When SQL Optimizer tests indexes, the indexes are physically created on the database and dropped after SQL Optimizer executes the statement. When you test run index alternatives, you must select a file group in which to store the created indexes.
Important: Since indexes are physically created on the database, this process may impact database performance and the performance of other SQL statements.
To execute index alternatives
- Select the Optimize SQL tab in the main window.
- Select a SQL Rewrite session.
-
Select an index set in the Alternatives pane.
-
Click
.
Tip: Click
to test run all index alternatives.
-
Select a filegroup in which to store the created indexes. Review the following for additional information:
Index Information Description Index Shows SQL text of index selected. Filegroup Filegroup to store indexes created.
Note: Once you select the filegroup for the first index, SQL Optimizer automatically selects the same filegroup for the remaining indexes.
Sort in Temp DB Indicates location to store intermediated sort results used to build the index. Select ON to store results in the tempdb column and OFF to store results in the filegroup or partition schema.
Note: Review the information in the DDL Script pane.
Troubleshoot Optimization Results
After you optimize the original SQL statement and determine the best alternatives, you may find the performance of the selected statements unsatisfactory in your database environment.
To remedy this, check if SQL Optimizer reached any of the optimization quotas for your original SQL statement. Select SQL Information in the Execution Plan pane and select Alert to view this information. Make adjustments to the intelligence, optimization, hints, or quota setting options if SQL Optimizer has reached any of the quotas. To access these settings, click 
Additionally, review the execution plan of the original and optimized SQL statements to check if you need to change the database structure.
Automatically Optimize SQL Statements
Retrieve Run Results
This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
To retrieve run results
- Right-click the Alternative Details or SQL Text pane and select Run Result.
-
Click
.
Tip: Click
to copy the SQL statement to execute in another Quest Software product.
Create User Defined Alternatives
In addition to using SQL Optimizer to generate alternative statements, you can create user defined alternatives. You can create user defined alternatives using SQL text from your original SQL statement or from SQL text of an alternative SQL Optimizer generated.
Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
To create a user defined alterative
- Select the Optimize SQL tab in the main window.
- Select a SQL Rewrite session.
-
Enter the SQL statement in the in the Alternative Details pane.
-
Click
.
-
Edit the statement in the SQL Text pane to create a user defined alternative.
Note: To create a user defined alternative using SQL text of an alternative SQL Optimizer generated, right-click an alternative and select User Alternative.
Tip: You can create virtual indexes for user defined alternatives. See Create Virtual Indexes for more information.
Caution: SQL Optimizer does not check that user defined alternatives are semantically equivalent to your original SQL statement. Review the execution statistics for user defined alternatives to compare the results with your original statement. See for more information.