SQL Server 2008 query hint: Optimize for Unknown

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.’

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s