A new query hint for SQL Server 2008 is: Optimize for Unknown
The query hint ‘Option (Optimize for (@Par = value))’ can also be used for SQL Server 2005.
‘Optimize for Unknown instructs the query optimizer to use statistical data instead of the initial values for all local variables when the query is compiled and optimized, including parameters created with forced parameterization.’ Source: Query Hints
Basicly the query optimizer will ignore the parameter value during query optimization and will use the same query execution plan for each parameter value.
‘Optimize for Unknown’ is the new problem solving solution for ‘Parameter Sniffing’
(A good example of Parameter Sniffing)
Note: ‘If OPTIMIZE FOR @variable_name = literal_constant and OPTIMIZE FOR UNKNOWN are used in the same query hint, the query optimizer will use the literal_constant that is specified for a specific value and UNKNOWN for the remaining variable values. The values are used only during query optimization, and not during query execution.’