OPTIMIZE FOR is used for making a good plan for specific query. A classical example is a report to skewed data that is run very often with same parameters. In such a scenario, it could be useful to optimize the query for the most common parameter. This is a trade-off, as other queries with different parameter are going to get worse a plan.

If you are suffering from parameter sniffing, you could use OPTIMIZE FOR UNKNOWN, OPTION RECOMPILE or local variables. None of these is a silver bullet, so bencmark the queries carefully. Make sure the issue really is parameter sniffing and not, say, out-of-date statistics.

A Microsoft blog discusses the issue with sample code, as a question right here on dba.so.

OPTIMIZE FOR is used for making a good plan for specific query. A classical example is a report to skewed data that is run very often with same parameters. In such a scenario, it could be useful to optimize the query for the most common parameter. This is a trade-off, as other queries with different parameter are going to get worse a plan.

If you are suffering from parameter sniffing, you could use OPTIMIZE FOR UNKNOWN, OPTION RECOMPILE or local variables. None of these is a silver bullet, so bencmark the queries carefully. Make sure the issue really is parameter sniffing and not, say, out-of-date statistics.

A Microsoft blog discusses the issue with sample code, as a question right here on dba.so.

OPTIMIZE FOR is used for making a good plan for specific query. A classical example is a report to skewed data that is run very often with same parameters. In such a scenario, it could be useful to optimize the query for the most common parameter. This is a trade-off, as other queries with different parameter are going to get worse a plan.

If you are suffering from parameter sniffing, you could use OPTIMIZE FOR UNKNOWN, OPTION RECOMPILE or local variables. None of these is a silver bullet, so bencmark the queries carefully. Make sure the issue really is parameter sniffing and not, say, out-of-date statistics.

A Microsoft blog discusses the issue with sample code, as a question right here on dba.so.

OPTIMIZE FOR is used for making a good plan for specific query. A classical example is a report to skewed data that is run very often with same parameters. In such a scenario, it could be useful to optimize the query for the most common parameter. This is a trade-off, as other queries with different parameter are going to get worse a plan.

If you are suffering from parameter sniffing, you could use OPTIMIZE FOR UNKNOWN, OPTION RECOMPILE or local variables. None of these is a silver bullet, so bencmark the queries carefully. Make sure the issue really is parameter sniffing and not, say, out-of-date statistics.

A Microsoft blog discusses the issue with sample code, as a question right here on dba.so.