Shulou(Shulou.com)06/01 Report--

In this article, we will introduce two concepts available in SQL Server, which are noteworthy techniques when using SQL Server.

1. OPTIMIZE FOR Unknown

The OPTIMIZE FOR hint (hint) has been added in the SQL Server 2005 release to allow DBA to determine literal values for cardinality evaluation and optimization. If we have a table with skewed data distribution, OPTIMIZE FOR can be used to optimize general values that provide reasonable performance for a wide range of parameter values. When performance is not the best for all parameter values, it may be preferable for all scenarios to have the same execution time, depending on the parameter values passed in during the initial compilation, rather than sometimes searching (seek for better selective parameter values) and sometimes scanning (scan for selective general parameter values).

Unfortunately, OPTIMIZE FOR only allows literals. If the variable is a similar date-time (datetime) or ordinal number (whose nature increases over time), any fixed values determined will soon become obsolete and have to be modified to determine a new value. Even if the range of the parameter remains relatively stable over time, you have to experiment and find a good enough universal value when providing a literal value, which is sometimes difficult or time-consuming.

Finally, providing a value for OPTIMIZER FOR will affect the choice of the plan by changing the predicate cardinality evaluation that uses this parameter. In the OPTIMIZE FOR prompt, if you provide a non-existent or rare value, then you reduce the cardinality estimate, which will affect the cost and the choice of the final plan.

If you only want an "average" value and don't care what it is, the OPTIMIZE FOR (@ variable_name UNKNOWN) hint will cause the optimizer to ignore this parameter value that affects the cardinality evaluation. Instead, a bar chart is used, and the cardinality assessment will be based on a fixed selectivity assessment of density, key information, or dependent predicates. This leads to a predictable assessment that does not require DBA to monitor and change parameter values all the time to maintain consistent performance.

The syntax change will tell the optimizer to ignore all parameter values by identifying the OPTIMIZE FOR UNKNOWN and omitting parentheses and variable names. Determine that 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 more predictable query performance may be given.

2. QUERYTRACEON and QUERYRULEOFF

In some scenarios, developers may recommend using trace flags (trace flag) to avoid query planning or optimizer problems. Or they may find that disabling a particular optimizer rule prevents a particular problem from happening. Some trace flags are so common that it is difficult to predict whether turning on these trace flags can solve all the query problems, or whether the problem is only for the specific queries studied. Similarly, most optimizer rules are not inherently bad, and disabling them system-wide can lead to performance degradation in other areas.

In SQL Server 2008, you can turn on a trace flag while a particular query is running, or disable an optimizer rule only during query compilation by following an unarchived QUERYTRACEON or QUERYRULEOFF prompt.

Select @ v_test=c1 from T1 where C1 option (recompile,querytraceon 2389)

Select @ v_test=c1 from T1 where C1 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 that the rule is fully understood and the possible consequences of disabling it. The database owner usually has sufficient permissions to create a plan guide (plan guide), while creating a plan guide with QUERYTRACEON/QUERYRULEOFF prompts requires sysadmin permissions, because changing these settings may have system-wide rather than database-wide implications.

Conclusion

Finally, it is important to know when to use these query optimization or query tuning techniques in your environment. Please analyze the specific situation and conduct adequate testing before using these techniques. .

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.