Two Options for Query Optimization with SQL Server

By Pinal Dave on November 12, 2014


Working with SQL Server is always a challenge. As developers try to troubleshoot SQL Server performance problems, the first step that take is to look at the queries. This is the most basic step and most important step for most developers. Developers love these challenges of optimization because they can get the maximum visible performance improvements in their environments. These activities give them the maximum visibility–even inside their organizations–when they are troubleshooting customer problems. In this blog post, let me take a stab at two concepts of query optimization that are available for SQL Server. These are techniques hidden inside SQL Server which are worth noting.

OPTIMIZE FOR Unknown

SQL Server 2005 added the OPTIMIZE FOR hint that allowed a DBA to specify a literal value to be used for the purposes of cardinality estimation and optimization. If we have a table with skewed data distribution, OPTIMIZE FOR could be used to optimize for a generic value that provided reasonable performance for a wide range of parameter values.  While the performance may not be the best for all parameters, it is sometimes preferable to have a consistent execution time instead of having a plan that did a seek in one case (for a parameter value that was selective) and a scan for another case (where the parameter value is very common), depending on the value passed during the initial compilation.

Unfortunately, OPTIMIZE FOR only allowed literals. If the variable was something like a datetime or order number (which by their nature tend to be increasing over time), any fixed value that you specify will soon become out of date and you must modify the hint to specify a new value. Even if the parameter is something whose domain remains relatively static over time, the fact that you must supply a literal means that you must experiment and find a value that is a good “general purpose” value to specify in the hint. Sometimes this is difficult or time consuming to get right.

Ultimately, supplying an OPTIMIZE FOR value influences plan selection by changing the cardinality estimates for the predicate using that parameter. In the OPTIMIZE FOR hint, if you supply a value that does not exist or is infrequent in the histogram, you reduce the estimated cardinality; if you supply a common value, then you increase the estimated cardinality. This affects cost and ultimately plan selection.

If all you want to do is pick an “average” value and you don’t care what the value is, the OPTIMIZE FOR (@variable_name UNKNOWN) hint causes the optimizer to ignore the parameter value for the purposes of cardinality estimation. Instead of using the histogram, the cardinality estimate will be derived from density, key information or fixed selectivity estimates depending on the predicate. This results in a predictable estimate that doesn’t require the DBA to constantly have to monitor & change the value to maintain consistent performance.

A variation of the syntax tells the optimizer to ignore all parameter values. You simply specify OPTIMIZE FOR UNKNOWN and omit the parenthesis and variable name(s).  Specifying OPTIMIZE FOR causes the ParameterCompiledValue to be omitted from the showplan XML output, just as if parameter sniffing did not happen.  The resultant plan will be the same regardless of the parameters passed, and may give more predictable query performance.

QUERYTRACEON and QUERYRULEOFF

There are some situations where the development team may suggest using a trace flag as a workaround for a query plan/optimizer problem. Or they may also find that disabling a particular optimizer rule prevents a particular problem. Some trace flags are generic enough that it is hard to predict whether turning the trace flag on is a good general solution for all queries or whether the problem is likely specific to the particularly query which was investigated. Similarly, most of these optimizer rules are not inherently bad and disabling on the system as a whole is likely to cause a performance degradation somewhere else.

In SQL Server 2008 you can now enable a trace flag only for the duration of the query or disable an optimizer rule only for that query compilation by using the undocumented QUERYTRACEON or QUERYRULEOFF hints as shown below:

SELECT @cpu_count = COUNT (DISTINCT pc.performance_instance_name)
FROM dob.performance_counters AS pc
INNER JOIN my.counters s ON s.counter_id = pc.counter_id
WHERE …
OPTION (QUERYTRACEON 2389)

SELECT @cpu_count = COUNT (DISTINCT pc.performance_instance_name)        
FROM dbo.performance_counters AS pc        
INNER JOIN my.counters s ON s.counter_id = pc.counter_id         
WHERE …        
OPTION (QUERYRULEOFF OmitMyIdx)

 

The second shows the syntax, but will probably result in a “no plan” error. QUERYRULEOFF should not be used without prior discussion with a developer to ensure complete understanding of the rule and possible side effects of disabling it. While a database owner would normally have sufficient permissions to create a plan guide, creating a plan guide which uses the QUERYTRACEON/QUERYRULEOFF hints requires sysadmin permissions because changing these settings may have system-level implications as opposed to being limited to database scope.

Conclusion

As we wrap up this blog, it is important to know when to use these options of query optimization or query tuning techniques in your environments. Please evaluate on a case-by-case basis and do enough testing before using them. I am sure the learning will never stop as we explore the next versions of SQL Server being packed with tons of additional features. Future blogs will discuss many of these extensions.

Related Posts

Leave a Reply