It contains a worked example showing step-by-step how parameter embedding and constant folding can work to improve an execution plan.I think the first point I would make is that option(recompile) is not necessarily an optimisation, I'd view it as a 'change of behaviour', which can affect performance either way. More information and background in my article (linked above) Parameter Sniffing, Embedding, and the RECOMPILE Options. When you run the same query without OPTION (RECOMPILE) the same simplifications are not possible because SQL Server cannot safely embed the parameter values, since the plan might be reused for different values. Your plans are large and anonymized, but I would suggest parameter embedding and the resulting major simplifications it can enable are responsible for the dramatic performance improvement. I note your database has forced parameterization enabled. Note the CONVERT_IMPLICIT in your screenshot to convert the supplied varchar value to nvarchar. For example, any type conversions or complex expressions the value is part of can be evaluated early (constant folding). This might sound trivial, but can enable important simplifications.
SQL Server replaces any parameters with their literal values before optimization. The second thing it enables is the Parameter Embedding Optimization. The newly-generated plan is not cached for reuse. The main thing OPTION (RECOMPILE) does is to compile a plan for the current values of any parameters, rather than reusing any cached plan. However, I believe the query plan hashes differ and the cached plan will not be reused since there are more changes than just the values passed in to the filtering clauses. I did think to generate the "fast" plan in the hopes that it would be stored in the cache and get reused when the OPTION RECOMPILE is not specified anymore. The first run is fast, the second is slow. I tried running the query once with OPTION (RECOMPILE) and then removing the hint and immediately running the query again (same parameters both times). Also, the anonymized plan for the OPTION RECOMPILE execution is here. You can find the anonymized plan for the slow execution here. I've tried to search online and see if it does "force" a predicate pushdown but I couldn't find anything specific about this. Now, my question is why would the OPTION RECOMPILE plan behave differently than when just generating a new plan for a new query, with the same parameters / values passed to it. You can see the same data access for the same table, below and the part of the more efficient OPTION RECOMPILE plan. In the OPTION RECOMPILE version, those parameters are pushed down to the execution plan (I'm guessing it's called Predicate Pushdown) and filtered directly in the first step, when data is read from disk.įrom what I looked at, this is my conclusion for why this is happening and why the OPTION RECOMPILE plan is more efficient. The nested levels contain themselves other TVF's with parameters determined from OUTER APPLY'ed queries. The FILTER operator has all the filtering parameters that come with the procedure ( unique_value and some_value) and some other filtering parameters that are determined in the nested levels.
In the plan without OPTION RECOMPILE, I have a part in my plan where the bulk of the time is spent moving around useless data which is later filtered by the FILTER operator (you can see 0B coming out of the FILTER). I've inspected both plans and noticed that for the plan with OPTION (RECOMPILE) the parameters that are passed. I've made sure to clear the plan cache before this happens and even when generating a new plan, it's suboptimal, however, the OPTION (RECOMPILE) one is fast. I've been trying to understand why is it that when I run the base query with OPTION (RECOMPILE) it works very fast, however when I run it without this OPTION, it runs very slow.
#Sql server option recompile code#
I have a SQL query that is made out of a nested mess of views and table-valued functions going on for at least 4 levels deep ( I didn't have the time or patience to go through it all, it's hundreds of lines of code at each level).
0 kommentar(er)
