2/3/2024 0 Comments Sql server recompile view![]() Hi,I increased one of my base tables column which is referenced in viewI noticed sql server didn't recognized this change and its still showing old field size in the view.I can simply drop and create it again. All ForumsĬan we recompile view instead of drop and create We've got lots of great SQL ServerĮxperts to answer whatever question you can come up with. In the meantime, ensure you only use OPTION (RECOMPILE) when it truly makes sense, and you have given it full consideration.Site at. 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.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 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |