It is not always possible to anticipate when the optimizer will decide to do this. This means literals or expressions can be duplicated in the query tree, meaning an embedded LOB value can appear more than once and each instance will require a fresh copy of the large value. Adding OPTION (RECOMPILE) is the equivalent of doing that.Īnother relevant factor is the optimizer makes no general guarantees about the number of executions or exact timing of scalar expression evaluations. The broader point is that you probably wouldn’t embed a 512MB string literal in a query, submit it in a query, and expect good performance. There are other internal details like the time taken to compute a hash of the constant value when storing it in a memo group. For one, embedding the value of at runtime requires making at least one copy of the entire string, which is resource-intensive. With a recompile hintĪdd OPTION (RECOMPILE) to the previous query: DECLARE varchar (max ), varchar (max ), varchar (max ) SET = 'x' SET = 'y' SET = + REPLICATE (, 512 * 1024 * 1024 ) SELECT LEFT (, 1 ) OPTION (RECOMPILE )
#SQL SERVER RECOMPILE FREE#
You are free to construct a LOB of any size if you really want to hurt your server that way. I will note in passing that LOB variables and parameters are not limited to 2GB. It is not unheard of for people to read complete files or XML/JSON into a variable using OPENROWSET.BULK for example. That executes in around 3 seconds, with the vast majority of the time spent constructing the large string.Īnd it is a large string, but not outlandishly so. ExampleĬonsider the following toy query, which creates a 512MB LOB string then returns the first character: DECLARE varchar (max ), varchar (max ), varchar (max ) SET = 'x' SET = 'y' SET = + REPLICATE (, 512 * 1024 * 1024 ) SELECT LEFT (, 1 ) This is often very useful for plan quality, but there is a potential drawback when large object types (LOBs) are in play. When PEO is used, SQL Server takes the value of any variables and parameters and embeds the runtime values in the query text, pretty much as if you had entered them by hand before compiling. The point of this short post is to draw your attention to another side-effect of adding OPTION (RECOMPILE) - the parameter embedding optimization (PEO).
Recompiling every time is very likely to save more time and resources than it costs overallĪll that is fairly well-known.The cost of recompiling the statement is much less than the expected execution time.
The plan might be expected to change over time.Optimize for unknown doesn’t give a good result.No good single value exists for the parameter to use in a hint.A good enough plan for the query is very sensitive to one or more parameters.It sometimes makes sense to add OPTION (RECOMPILE) to a query.
0 kommentar(er)
