OPTION(RECOMPILE) and OPTION(FAST n) [duplicate]

SQL server option recompile is one of the oldest and most traditional technique to not cache the query plans and compile your stored procedure or queries every single time to get optimal performance.

These hints have different purpose:

OPTION(FAST n) Specifies that the query is optimized for fast retrieval of the first number_rows It's not designed to produce the best plan every time but simply induce the optimizer to "think" that the result cardinality is less than actual. So this hint isn't related to "parameter sniffing" and its goal is different from producing the optimal plan for given query.

OPTION(RECOMPILE) tells the server not to cache the pan for given query. This means that another execution of the same query will require to elaborate a new(maybe different) plan. This is used in the queries with parameters to prevent parameter sniffing issue. This means that your query should have different plans depending on the parameter provided. For example when you query smth filtering on the capital of some country your query returns almost all the records from table and you prefer table scan in this case. But when you filter on the small city you get back few rows and prefer index seek + lookup.

If you don't use OPTION(RECOMPILE), the first time your parameterized query is executing its parameter is "sniffed" and the plan is made based on this parameter value. The subsequent executions with different parameters will always use the same plan and this can be unacceptable for you. So in this case you use the OPTION(RECOMPILE) that will lead to optimal cardinality estimation of the result set at every query execution.

Summary.

Use of OPTION(FAST n) generally will not lead to optimal plan elaboration and will provoke fixed (and wrong) cardinality estimation of the result set equals to n. Use of OPTION(RECOMPILE) generally will lead to optimal plan choice and will provide different (and generally correct) cardinality estimation at every query execution, base every time on the value(s) of parameteres provided.

 UPDATE

This repro shows how the query that uses RECOMPILE option does not cache it's plan:

declare @prod_n nvarchar(255) = N'HJ-1428'

select * /*4042FBFE-6ED4-406E-90F8-73C5EBD3920F*/

from [Production].[Product]

where ProductNumber = @prod_n

option(recompile);

declare @sql nvarchar(4000); 

set @sql =

N'select * /*4042FBFE-6ED4-406E-90F8-73C5EBD3920F*/

from [Production].[Product]

where ProductNumber = @prod_n

option(recompile);'

exec sp_executesql @sql, N'@prod_n nvarchar(255)',@prod_n; 

select *

from sys.dm_exec_query_stats qs

    cross apply sys.dm_exec_sql_text(qs.plan_handle) qt

    cross apply sys.dm_exec_query_plan(qs.plan_handle) qp

where qt.text like '@42FBFE-6ED4-406E-90F8-73C5EBD3920F%' 

  and qt.text not like '%sys.dm_exec_query_stats%'; -- uncomment this to get the plan for given query that looks for the plan

Here I use a guid to distinguish my query, and I use and qt.text not like '%sys.dm_exec_query_stats%' condition to exclude the plan of the last query that looks for the plan of the two first queries.

I used both local variable and parameter in my queries and none of them put the plan in cache.