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
-
Click
in the main menu.
- Select Optimize SQL | SQL Rewrite | Hints.
-
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).
Quota Options
This topic may not include a description for every field in the screen or dialog.To set optimization quota option
-
Click
- Select Optimize SQL | SQL Rewrite | Quota
-
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.
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
-
Click
- Select Optimize SQL | SQL Rewrite | Result.
-
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. -
Index Generation
To set index options
-
Click
- Select Optimize SQL | Index Generation | Options.
-
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