OPTIMIZE FOR UNKNOWN in SQL Server 2005
Just helped a performance problem with the information below and thought of sharing since it might be useful in some situations..
OPTIMIZE FOR UNKNOWN introduced in SQL Server 2008 gives this benefit:
"....This hint directs the query optimizer to use the standard algorithms it has always used if no parameters values had been passed to the query at all. In this case the optimizer will look at all available statistical data to reach a determination of what the values of the local variables used to generate the queryplan should be, instead of looking at the specific parameter values that were passed to the query by the application...."
But this hint is not available for SQL Server 2005, so the option available in SQL Server 2005 as an alternative to the query hint given above is to disable parameter sniffing server wide using trace flag 4136.
But the side effect is parameter sniffing is disabled instance wide so queries which might benefit from parameter sniffing will not be able to do so.
More info on trace flag 4136: