In continuation of the previous recipe, the query hints are useful when you are in control on table design and data, and not to mention about data distribution, which is a critical aspect to obtain optimized performance. However, the query hints implementation is not a compulsion to use at all times. The hints will always provide a short-term result—that satisfies the existing result set—which may not be efficient over a period of time due to the data changes on the table. A new enhancement has been added to the OPTIMIZE FOR UNKNOWN
query hint to include new statistical data to determine values for local variables during the query optimization instead of default values. Similarly, if the data is spread across multiple tables (skewed data distribution), the OPTIMZIZE FOR
clause could be used to optimize for a generic value. In such situations, this query hint might provide reasonable performance for a wide range of parameter values.
In this...