サポートと今すぐチャット
サポートとのチャット

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

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

  1. Select the Optimize SQL tab in the main window.
  2. Select a SQL Rewrite session.
  3. Select an index set in the Alternatives pane.

  4. Click .

    Tip: Click to test run all index alternatives.

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

 

Related Topic

Generate Index Alternatives

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 and select Optimize SQL.

Additionally, review the execution plan of the original and optimized SQL statements to check if you need to change the database structure.

 

Related Topics

Optimize SQL Statements

Automatically Optimize SQL Statements

Test Run SQL Alternatives

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

  1. Right-click the Alternative Details or SQL Text pane and select Run Result.
  2. 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

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

  4. Click .

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

 

Related Topics

Compare SQL Statements

Test Run SQL Alternatives

関連ドキュメント

The document was helpful.

評価を選択

I easily found the information I needed.

評価を選択