This is especially beneficial to stored procedures that contain many statements. This is advantageous because when input parameters change in a stored procedure the majority of the stored procedure can still utilize the cached execution plan. With SQL Server 2005+, recompilation now occurs at the individual statement level rather than stored procedure level. A similar sort of phenomenon also happens in temp tables. When the tables (in which underlying queries rely on) change significantly, SQL Server detects this change (using auto update statistics) and marks the dependent queries to be recompiled so the next execution can create an optimal execution plan. This happens because temp tables are treated just like regular tables by the SQL Server Engine. In the meantime, ensure you only use OPTION (RECOMPILE) when it truly makes sense, and you have given it full consideration.If you landed on this article, then you most likely know that temp tables can cause recompilation. I would argue that embedding large objects via PEO will rarely, if ever, pay dividends. Embedding the 512MB string is followed by evaluating DATALENGTH during compilation, which is itself an expensive operation. This means evaluating an expression at compile time, for example in a variation of the example above. The final thing I want to mention is constant folding. 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 ) 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. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |