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.
A: The answer to this question is yes and no. Unfortunately, using only OPTION (RECOMPILE) as a single or uniform solution can be even more problematic. 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. In fact, if you have a system with a lot of dynamic statements or recompilations, you might make things worse.
As a general practice, I try not to use recompilation. If I do use it, I tend to do so sparingly—only if I can’t come up with a better solution. 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. Sometimes I can create an index (usually a covering index) and make the plan more stable or consistent without requiring recompilation. In other cases, OPTION (RECOMPILE) doesn’t help the execution plan because the statement itself is the problem. 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 this case, the WHERE clause looks something like the following:
WHERE (ColumnX = @variableX OR @variableX IS NULL) AND (ColumnY = @variableY OR @variableY IS NULL) AND (ColumnZ = @variableZ OR @variableZ IS NULL) …
When the procedure looks like this, I call it a multipurpose procedure.
The simple fact is that when the T-SQL statement itself is poorly written, even recompilation can’t help in some cases. 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.
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. To execute the constructed statement, I use either dynamic string execution or sp_executesql. Determining which one to use requires knowledge of your data, some plan analysis, and thorough testing.
As 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). 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. 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.
For 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?”
Recompilation is a complicated subject that requires you to know three key things:
- Your data
- Your workload
- How SQL Server works
The last item is the one that gives people the most trouble. We hope this Q&A helps you get closer!
