It will not be saved and certainly won’t be reused. This option will cause a new plan to be generated just for this one execution. First, we can ascertain whether recompiling will actually help performance by executing the procedure with the RECOMPILE option:ĮXEC get_sales_range 66666 WITH RECOMPILE WHERE SalesOrderID < we want to make sure we get a new plan every time we run this query, with potentially vastly different values for we can add the RECOMPILE hint as shown:įor stored procedures, we have three options. Using FORCED parameterized (discussed in the previous article), this query will be parameterized.

For individual statements, such as with PREPARED plans that have been autoparameterized, we can add the RECOMPILE hint to a query. If inappropriate use of parameter sniffing is a problem, a simple solution is to just tell SQL Server to come up with a new plan. However, most people use the term ‘recompile’, so that’s what I’ll use here. Usually, we call the act of coming up with a new plan ‘recompiling’, but it probably should be called ‘reoptimizing’. So, what can we do when parameter sniffing is a problem? We can force SQL Server to come up with a new plan. If a plan is based on an initial value that causes the optimizer to generate a plan appropriate for that value, and then the same plan is used for a different value, the plan may no longer be optimal. I ended the discussion with a look at an inappropriate reuse of a plan, which can happen when SQL Server applies parameter sniffing in the wrong situations. We looked at three types of query plans: adhoc, prepared, and procedure. In my previous article, I talked about SQL server diagnostics, including the various options that SQL Server has for reusing a query plan. In these posts I’ll share with you how I look at some of the features or aspects of SQL Server, sometimes along with a bit of historical perspective I’ve seen a lot of changes over the years (and decades!) and versions of this incredible product. SQL Server has been around over 30 years, and I’ve been working with SQL Server for almost as long. Be sure to read Kalen's first post on this topic. For more information, see Recompile a Stored Procedure.This is part of a SQL Server Internals Plan Caching series. Also use OPTIMIZE FOR when you create plan guides.

OPTIMIZE FOR can counteract the optimizer's default parameter detection behavior. The data type of must be implicitly convertible to the data type that references in the query. can be of any SQL Server system data type that can be expressed as a literal constant. is used only during query optimization, and not as the value of during query execution. Specifies that the Query Optimizer uses statistical data instead of the initial value to determine the value for a local variable during query optimization.Ī literal constant value to be assigned for use with the OPTIMIZE FOR query hint. The value is used only during query optimization, and not during query name of a local variable used in a query, to which a value may be assigned for use with the OPTIMIZE FOR query hint. Instructs the Query Optimizer to use a particular value for a local variable when the query is compiled and optimized. Transact-SQL syntax conventions Syntax ::= Because the SQL Server Query Optimizer typically selects the best execution plan for a query, we recommend only using hints as a last resort for experienced developers and database administrators.

Sql option recompile