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.While "throw more memory at it" can help some database performance issues, at least temporarily, if your bottlenecks are very CPU bound rather than memory and/or IO bound then adding more memory will have very little effect.It sometimes makes sense to add OPTION (RECOMPILE) to a query. There is sometimes more to optimisation than preferring seeks over scans and so forth, sometimes an index scan is more efficient than many executions of seek operations, and the cost estimates upon which the percent figures you are looking at are calculated are that (estimates) at best (a useful guide but sometimes far from at all accurate). SELECT += 'EXEC sp_refreshview '''++''''+CHAR(10) FROM sys.objects WHERE IN ('V') ĮXEC execute plans show 80+ percent of the run time is on a clustered index seek so I don't think there is much more I can do to optimise the stored procedures. ![]() You can similarly mark them as needing to be reassessed to make sure stored plans and other meta-data is not stale with sp_refreshview, by small modifications to either the cursor or ad-hoc SQL methods shown above: DECLARE NVARCHAR(MAX) = '' ![]() SELECT += 'EXEC sp_recompile '''++''''+CHAR(10) FROM sys.objects WHERE IN ('P', 'FN', 'IF') ĮXEC I find this form sometimes throws people due to looking set-based but building the string up iteratively, and not being a standard SQL pattern)Īnother set of objects that might be a similar concern here is views. Or you could produce ad-hoc SQL and run that via EXEC, takes less code which might be marginally more efficient: DECLARE NVARCHAR(MAX) = '' You can run sp_recompile on everything by using a cursor to produce ad-hoc SQL for each and run it, if you think that will help: DECLARE C CURSOR FOR (SELECT FROM sys.objects WHERE IN ('P', 'FN', 'IF'))
0 Comments
Leave a Reply. |