I was checking the performance of
OPTION(FAST n) and their behaviors are surprisingly different.
OPTION(FAST n) is much faster then
OPTION(FAST n)? create new execution plan every time?
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 +
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.
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.
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.
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.