Problems with Local Variables

Q: Using local variables in statements and procedures can cause poor cardinality estimates. Alternative solutions can include rewriting the query to use literals instead of variables, using sp_executesql with a constructed statement that uses literals, or using a stored procedure with parameters (not local variables). Does using these options affect the use of statistics or change the way SQL Server behaves?

A: The problem with local variables is that they have unknown values at the time the query plan is compiled. As a result, SQL Server uses unknown statistics information (i.e., nonspecific value) during compilation.

Generating a specific statistic from a nonspecific value seems complicated, but it’s fairly easy to do in SQL Server. For every statistic, SQL Server keeps averages in addition to the histogram (which has specific values). According to the MSDN white paper “ Statistics Used by the Query Optimizer in Microsoft SQL Server 2008,” the histogram-based value (and the value obtained through parameter sniffing) is typically better. However, because a lot of commands and procedures (sp_executesql and stored procedures in general) save the plan based only on the first execution (when one isn’t already found in the plan cache), then subsequent executions can have performance problems if their most optimal plan isn’t the same as the one that the first execution created, compiled, or saved.

The end result is that you might need to force SQL Server to not save the compiled plan in the plan cache so that a plan is compiled on each execution. One clever way to accomplish this is through dynamic string execution, which is when you build a string to execute with the command EXECUTE('string').

Dynamic string execution follows essentially the same rules as an ad hoc statement (which can be parameterized, saved, and reused), but the rules are so strict that the compiled plan is unlikely to be saved. As a result, dynamic string execution statements are typically recompiled on each execution, with their literal values. This means that you pay the overhead of recompilation but get the (likely) benefit of a better plan. However, you have to be careful of SQL injection.

For information about how to eliminate SQL injection by protecting parameters and using EXECUTE AS (with a login-less database user), see my blog post “ Little Bobby Tables, SQL Injection and EXECUTE AS.” My SQLskills.com blog also includes the general category Optimizing Procedural Code. But because there are a variety of parameter-related problems (each with different solutions, as well as different pros and cons for each solution), finding the best solution is a bit more confusing.