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

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

Hints Options

By adding hints, you can force SQL Optimizer to choose a particular execution plan. Hints added to optimized SQL statements are in purple (default color) and display in the Alternative Details pane.

To set hint options

  1. Click in the main menu.

  2. Select Optimize SQL | SQL Rewrite | Hints.
  3. Review the following for additional information:

    Group Hints Description

    HASH GROUP

    Select this checkbox to use hashing to compute a GROUP BY clause.

    ORDER GROUP

    Select this checkbox to use ordering to compute a GROUP BY clause.
    Join Hints Description

    LOOP JOIN

    Select this checkbox to use a LOOP JOIN to join the rows of two tables.

    HASH JOIN

    Select this checkbox to use a HASH JOIN to join the rows of two tables.

    MERGE JOIN

    Select this checkbox to use a MERGE JOIN to join the rows of two tables.
    Union Hints Description

    CONCAT UNION

    Select this checkbox to concatenate the data sets retrieved from all UNION operations.

    HASH UNION

    Select this checkbox to use HASH JOIN to join the data sets retrieved from all UNION operations.

    MERGE UNION

    Select this checkbox to use MERGE JOIN to join the data sets retrieved from all UNION operations.
    Table Hints Description

    INDEX

    Select this checkbox to use the index table hint to force the use of a particular index. This hint is applied during the SQL syntax transformation process and is included in the Syntax Transformation Quota.
    FORCESEEK

    Select this checkbox to specify that the query optimizer use only an index seek operation as the access path to the data in the table or view.

    FORCESCAN (for SQL Server 2008R2 SP 1) Introduced in SQL Server 2008 R2 SP1, this hint specifies that the query optimizer use only an index scan operation as the access path to the referenced table or view.
    NOEXPAND Select this checkbox to specify that any indexed views are not expanded to access underlying tables when the query optimizer processes the query.
    Other Hints Description

    FAST

    Select this checkbox to retrieve the specified number of rows

    FORCE ORDER

    Select this checkbox to join tables in the order specified in the query.

    ROBUST PLAN

    Select this checkbox to retrieve an execution plan that accommodates the maximum row size.

    OPTIMIZE FOR (SQL Server 2005)

    Select this checkbox to instruct the SQL Server query optimizer to use a particular value for a local variable when it compiles and optimizes the query. The value is used only during query optimization and not during query execution.

    During optimization, the values used for each variable in the SQL statement by the SQL Optimizer engine are taken from the data statistics in the histogram. The values it uses are the highest, middle, and lowest selectivity.

    EXPAND VIEWS Select this checkbox to specify that the indexed views are expanded and the query optimizer does not consider any indexed view as a substitute for any part of the query
    IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX (for SQL Server 2012) Select this checkbox to prevent the query from using a non-clustered xVelocity memory optimized columnstore index.
    QUERYTRACEON 2312/9481 (Enable or Disable SQL Server 2014 Cardinality Estimator)

    Select to enable the QUERYTRACEON trace flags 2312 and 9481, available in SQL Server 2014.

    When running compatibility level 120, trace flag 9481 forces the query optimizer to use the SQL Server 2012 version of the cardinality estimator when creating the query plan.

    When running compatibility level 110, trace flag 2312 forces the query optimizer to use the SQL Server 2014 version of the cardinality estimator when creating the query plan.

    Parallel Hint Description

    MAXDOP 1 (Maximum degree of parallelism)

    Select this checkbox to specify MAXDOP = 1.

    If you do not select this option, the default MAXDOP value is used (MAXDOP = 0).

 

Related Topics

Optimization Options

Quota Options

Result Options

Quota Options

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

To set optimization quota option

  1. Click in the main menu.

  2. Select Optimize SQL | SQL Rewrite | Quota
  3. Review the following for additional information:

    Syntax Transformation Quota

    Enter the maximum number of SQL statements generated by applying syntax transformation rules in the artificial intelligence engine.

    Default: 150

    Range: 1 to 99,999

    Note: The default value of 150 is normally sufficient. Increase the value for more complicated SQL statements with high levels of table joins or multiple levels of nested sub-queries.

    Hints Quota

    Enter the maximum number of SQL statements to which SQL Optimizer applies optimization hints.

    Note: This value determines the number of SQL alternatives generated by transforming the SQL syntax.

    Total Quota

    Displays the number of SQL statements generated during optimization.

 

Related Topics

Result Options

Result Options

Microsoft SQL Server provides the functionality to check SQL statements for compliance with the Ansi SQL 92 standard. This setting uses SQL Server functionality to generate only alternative SQL statements that are compliant with the standard.

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To set result options

  1. Click in the main menu.

  2. Select Optimize SQL | SQL Rewrite | Result.
  3. Review the following for additional information:

    Check compatibility to SQL-92 standard Select this checkbox to check which alternative SQL statements follow the SQL-92 Ansi standard.
    Level

    Select the SQL Server compliance level used to check for compatibility:

    • Entry—Checks for SQL-92 entry-level compliance.

    • Intermediate—Checks for SQL-92 intermediate-level compliance.

    • Full—Checks for SQL-92 full-level compliance.
    Generate compatible SQL only/ Show indicator Select whether SQL Optimizer only generates SQL alternatives compatible with the SQL-92 Ansi standard or generates all alternatives while marking SQL-92 compliant alternatives with an indicator.

 

Related Topic

Quota Options 

Index Generation

To set index options

  1. Click in the main menu.

  2. Select Optimize SQL | Index Generation | Options.
  3. Review the following for additional information:

    Option Description

    Top percentage rows from table:

    Enter the percentage of the table to use for the selectivity sampling.

    Range: 1 to 100

    Minimum number of rows:

    Enter the minimum number of rows to retrieve for selectivity sampling. If this number is higher than the calculation from the Top percentage rows from table field, SQL Optimizer retrieves this number of rows from the table.

    Range: 1 to 999,999

    Maximum number of rows:

    Enter the maximum number of rows to retrieve for selectivity sampling. If this number is lower than the calculation from the Top percentage rows from table field, SQL Optimizer retrieves this number of rows from the table.

    Range: 1 to 999,999

    Maximum number of columns in a composite index:

    Enter the maximum number of columns in a composite index.

    Range: 1 to 99

    Maximum number of indexes in an index set:

    Enter the maximum number of indexes in an index set.

    Range: 1 to 99

    Evaluate columns in SELECT list:

    Select to evaluate creating an index on columns in the SELECT list.
    Quota Description

    Index Generation Quota:

    Enter the maximum number of indexes to generate.

    Range: 10 to 9999

    Index Set Generation Quota:

    Enter the maximum number of index sets to generate by combining two or more indexes.

    Range: 1 to 99,999

    Index Prefix Description

    Default prefix of index name:

    Enter the prefix placed on the index name when SQL Optimizer generates index candidates.

    Default: QUEST_SX_IDX

 

Related Topics

Intelligence Options (Index Generation)

関連ドキュメント

The document was helpful.

評価を選択

I easily found the information I needed.

評価を選択