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 ) 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).
0 Comments
Leave a Reply. |