Two options in SQL Server query optimization

In this article, we'll cover the concepts available in two SQL Servers, which are noteworthy techniques when using SQL Server.

1. OPTIMIZE for Unknown

The OPTIMIZE for hint (hint) is added to the SQL Server 2005 version to allow the DBA to determine the literal value used for cardinality evaluation and optimization. If we have a table with a skewed data distribution, OPTIMIZE for can be used to optimize common values that provide reasonable performance for a wide range of parameter values. When performance is not the best for all parameter values, sometimes it is preferable to do a lookup (seek, for a better-selective parameter value), or to do a scan (scan, for the optional generic parameter value), and it may be desirable to have the same execution time for all scenarios, depending on the value of the parameter passed in during the initial compilation.

Unfortunately, OPTIMIZE for only allows literal values. If the variable is a similar datetime (datetime) or sequential number (its nature grows over time), then any fixed value that is determined will soon have to modify the hint to determine a new value because it becomes obsolete. Even though the parameter range remains relatively stable over time, you have to experiment with and find a good enough common value when providing literal values, which can sometimes be difficult or time-consuming.

Finally, providing a value for the optimizer for will affect the selection of the plan by altering the predicate cardinality evaluation that uses the parameter. In the optimize for hint, if you provide a nonexistent or rare value, you reduce the cardinality evaluation value, which will affect the cost and final plan selection.

If you only want an "average" value and don't care what the value is, the OPTIMIZE for (@variable_name UNKNOWN) hint will cause the optimizer to ignore this parameter value that affects cardinality evaluation. Instead, a histogram is used, and the cardinality evaluation is determined by a fixed selectivity assessment of density, critical information, or dependent predicates. This results in a predictable assessment that does not require the DBA to constantly monitor and change parameter values to maintain consistent performance.

The syntax change tells the optimizer to ignore all parameter values, just determine optimize for unknown and omit the parentheses and variable names. Determining optimize for will cause Parametercompiledvalue to disappear from the Showplan XML output, just as parameter sniffing (sniffing) did not occur. Regardless of the parameters passed, the final plan will be the same, and may give more predictable query performance.

2. Querytraceon and Queryruleoff

In some scenarios, developers may recommend tracking flags (trace flag) to avoid query plans or optimizer problems. Or, they may find that disabling a particular optimizer rule prevents a particular problem from occurring. Some trace flags are so common that it is difficult to foresee whether opening these trace flags is a good solution to all query problems, or whether the problem is only for specific queries of the study. Similarly, most optimizer rules are not inherently bad, and disabling the rule throughout the system may result in other aspects of performance degradation.

In SQL Server 2008, you can turn on a trace flag during a specific query run, or only disable one of the optimizer rules during query compilation by following an archive Querytraceon or Queryruleoff prompt.

Select @v_test =c1from t1 where c1=2 option (Recompile,querytraceon 2389);

Select @v_test =c1from t1 where c1=2 option (Recompile,queryruleoff omitmyidx);

The syntax shown in the second statement above may result in a "no plan" error. Queryruleoff should not be used without prior discussion with the developer to ensure full understanding of the rule and the possible consequences of disabling it. A database owner typically has sufficient permissions to create a plan guide, and the Querytraceon/queryruleoff hint creates a plan guide that requires sysadmin permissions, because changing these settings might have a system rather than a database-wide meaning.

Conclusion

Finally, it is important to know when to use these queries to optimize or query tuning techniques in your environment, before you use these technologies, analyze the situation and do enough testing.

Two options in SQL Server query optimization