Wednesday, February 6, 2013

OPTIMIZE FOR UNKNOWN and other query hints

Query Hints go in the OPTION clause at the end of a query.
SELECT <columnlist>
FROM <table>
WHERE <clause>
OPTION(query hint)
The only one I've really had cause to use in the past is MAXDOP 1 to prevent large queries unsuccessfully using multiple processors.

Some new ones (to me) I read about today, are...

  • KEEP PLAN - Reduces likelihood of plan recompilation by relaxing the recompile threshold.
  • KEEPFIXED PLAN - Tells optimiser not to recompile plan even if statistics have changed.
  • ROBUST PLAN - Use plan with maximum possible rowsize.
  • OPTIMIZE FOR (parameter value) - use this value as a local variable in query optimization
  • OPTIMIZE FOR UNKNOWN (New to SQL 2008)- Use statistical data to determine value for local variable in query optimization

Links :
OPTIMIZE FOR UNKNOWN – a little known SQL Server 2008 feature
How OPTIMIZE FOR UNKNOWN Works
Query Hints

No comments: