-3

I was checking the performance of OPTION(RECOMPILE) and OPTION(FAST n) and their behaviors are surprisingly different. OPTION(FAST n) is much faster then OPTION(RECOMPILE). Does OPTION(FAST n)? create new execution plan every time?

  • i read that post as well but i didn't get comparison between both OPTIONS? – sal Apr 19 '18 at 8:13
  • 1
    Have you looked up what each option does @SAL? – George.Palacios Apr 19 '18 at 8:14
  • 1
    You're comparing apples with oranges. – TT. Apr 19 '18 at 8:14
  • @TT. yes i appriciated your comment but i want lean about this if you can help – sal Apr 19 '18 at 8:16
  • 1
    Recompile: recompiles the statement each time it is executed, optimizing for any parameters that are supplied to the statement and taking into account current statistics and cardinalities. FAST n: creates an execution plan that is optimized to return the first n rows, if no such plan already exists. – TT. Apr 19 '18 at 8:18
6

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 '%4042FBFE-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.

|improve this answer|||||
  • 2
    Worth noting the additional overhead of OPTION(RECOMPILE) in terms of CPU and compile-time - it shouldn't be added to every query. – George.Palacios Apr 19 '18 at 9:10
  • @George.Palacios I just explained the difference between the hints and did not call everyone to use RECOMPILE. It's quite acceptable on DWH-servers, and where it's not, there are another solutions to address parameter sniffing – sepupic Apr 19 '18 at 9:15
  • 2
    Actually your description isn't correct. OPTION(RECOMPILE) tells the optimizer not to RETRIEVE the existing plan from the cache, and to instead generate a new one when the procedure or ad-hoc T-SQL is called. The Plan generated is still added to the cache. – George.Palacios Apr 19 '18 at 9:26
  • @George.Palacios Can you prove it with any repro or with the link that describes the things you wrote? If you want I'll update my answer with the repro that shows that the plan for the query that uses RECOMPILE option is NOT cached – sepupic Apr 19 '18 at 9:34
  • Ah this was my misunderstanding - I was confusing Statement level and Procedure level functionality with recompile hints. Apologies. – George.Palacios Apr 19 '18 at 10:54

Not the answer you're looking for? Browse other questions tagged or ask your own question.