The new plan is discarded immediately after execution of the statement. Any pre- existing plan even if it is exactly the same as the new plan, is not used. When used with a T-SQL statement whether inside an SP or adhoc, Option 2 above creates a new execution plan for that particular statement. There is no caching of this particular execution plan for future reuse. Once the SP is executed, the plan is discarded immediately. Any existing plan is never reused even if the new plan is exactly the same as any pre-existing plan for that SP. It can not be used at an individual statement level but at a stored procedure level only. When used in the T-SQL code of a particular stored procedure (SP), Option 1 compiles that SP every time it is executed by any user. Option2: Option (Recompile) Their functionality Lastly, some relevant cautionary notes for their usage are mentioned. First, the features are explained and then they are differentiated. The last item is the one that gives people the most trouble.This article attempts to differentiate between the uses of WITH RECOMPILE and OPTION (RECOMPILE) features in Microsoft SQL Server 2005/2008. Recompilation is a complicated subject that requires you to know three key things: You’ll also need to protect your code from SQL injection, which often requires other features, such as string protection (with the REPLACE() or QUOTENAME() functions), EXECUTE AS, or login-less users.įor more information about protecting against SQL injection, see my blog post “ Little Bobby Tables, SQL Injection and EXECUTE AS.” For more information about multipurpose parameters, see my blog post “ Stored Procedure Parameters giving you grief in a multi-purpose procedure?” If the distribution of the data for specific parameters causes the plan to change, use dynamic string execution (EXEC('string')) rather than sp_executesql to execute the statement. Determining which one to use requires knowledge of your data, some plan analysis, and thorough testing.Īs a simple rule of thumb, use sp_executesql if the plan chosen by each specific set of parameters is consistent (you’ll only know this through thorough testing). To execute the constructed statement, I use either dynamic string execution or sp_executesql. In these cases, I build the exact statement with only the non-null parameters, instead of using a single statement that includes numerous variables that are set to NULL. Sometimes, dynamically building the statement yields better results-especially in cases in which the number of supplied parameters (versus those that are null) changes from execution to execution. The simple fact is that when the T-SQL statement itself is poorly written, even recompilation can’t help in some cases. When the procedure looks like this, I call it a multipurpose procedure. In this case, the WHERE clause looks something like the following: WHERE (ColumnX = IS NULL) This is a common situation, which often occurs when the supplied parameters vary and a single procedure was created to satisfy all the possible parameter combinations (some of which might not be supplied at all). In other cases, OPTION (RECOMPILE) doesn’t help the execution plan because the statement itself is the problem. Sometimes I can create an index (usually a covering index) and make the plan more stable or consistent without requiring recompilation. For example, the optimal execution plan for some statements can vary between table scans and nonclustered index with (bookmark) lookups because of the volatility of the parameters, combined with the fact that a better index doesn’t exist. If I do use it, I tend to do so sparingly-only if I can’t come up with a better solution. In fact, if you have a system with a lot of dynamic statements or recompilations, you might make things worse.Īs a general practice, I try not to use recompilation. Although recompilation can be helpful for some statements, it’s a waste when it’s unnecessary, it can use too much CPU, and there are some statements that it just can’t help. Unfortunately, using only OPTION (RECOMPILE) as a single or uniform solution can be even more problematic. Q: Is it better to use OPTION (RECOMPILE) rather than dynamic SQL? Both options are likely to recompile each time, and forcing recompilation with OPTION (RECOMPILE) doesn’t have the security implications of dynamic string execution.Ī: The answer to this question is yes and no.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |