![]() ![]() This repro shows how the query that uses RECOMPILE option does not cache it's plan: declare nvarchar(255) = N'HJ-1428' ![]() 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. 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. So in this case you use the OPTION(RECOMPILE) that will lead to optimal cardinality estimation of the result set at every query execution. The subsequent executions with different parameters will always use the same plan and this can be unacceptable for you. 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. But when you filter on the small city you get back few rows and prefer index seek + lookup. 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. This means that your query should have different plans depending on the parameter provided. This is used in the queries with parameters to prevent parameter sniffing issue. This means that another execution of the same query will require to elaborate a new(maybe different) plan. OPTION(RECOMPILE) tells the server not to cache the pan for given query. So this hint isn't related to "parameter sniffing" and its goal is different from producing the optimal plan for given query. 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. OPTION(FAST n) Specifies that the query is optimized for fast
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |