Parameter Sniffing

Prior to compiling an execution plan for a stored procedure, SQL Server attempts to “sniff” (i.e., discern) the values of the parameters being passed into it and use those values when compiling the plan. When these values are being used to filter a query (i.e., as part of a WHERE or HAVING predicate), this allows the optimizer to produce a more precise execution plan tailored to the values being passed into the stored procedure (using the statistics histograms for the columns they're used to filter) rather than based solely on the average density of the relevant table columns. Generally speaking, this is a good thing and results in improved performance over older versions of SQL Server.

You can get into trouble, however, when an atypical parameter value is passed in when a plan is first compiled for a procedure. The plan that's cached in memory may be suboptimal for the majority of the values that will be supplied for the parameter. When more typical values are supplied, they may reuse the old plan and may take longer to execute than they would have if the plan had been tailored to them instead.

You have a few options in this situation. You can mark the procedure for automatic recompilation using the WITH RECOMPILE option. This will cause the procedure's plan to be rebuilt each time it's executed. If the compilation time is negligible, this can be an easy solution for dealing with parameter values that vary a great deal in terms of their distribution across a table column.

You can also execute a procedure using the WITH RECOMPILE option—again causing the plan to be rebuilt. Similarly, you can use the sp_recompile procedure to cause a procedure's plan to be rebuilt the next time it's executed.

You can also “disable” parameter sniffing by filtering your query using local variables to which you've copied the parameter values. Using local variables instead of procedure parameters to filter a query is generally a bad idea because it inhibits the use of an index's statistics histogram in computing selectivity, but there are exceptions to the rule. When the optimizer can't use the statistics histogram to compute the number of rows that may be returned by a particular filter criterion, it uses magic numbers—hard-coded estimates of the percentage of rows that will be returned based on the comparison operator used. In some rare cases, these estimates may be more accurate than using the histogram itself. One such case is when the value used to scan the histogram is atypical and results in a skewed estimate of the number of rows that will normally match the supplied parameter.

You can sometimes also reorganize a query that's affected by errant parameter sniffing such that it runs in a distinct execution context that receives its own execution plan. This leverages the fact that when a procedure executes a T-SQL block dynamically or calls another procedure, each gets its own execution plan. Methods of using this technique to deal with parameter sniffing idiosyncrasies include using sp_executesql, EXEC(), and breaking a procedure into multiple procedures. With the sp_executesql and multiple procedure approach, you can still benefit from plan reuse. With EXEC(), you are not likely to—it's likely that your plan will have to be recompiled with each execution. Depending on what you're doing and how long compilation takes, this may or may not be desirable.

You can also explicitly clear the procedure cache with DBCC FREEPROCCACHE. This will cause all compiled plans to be tossed from memory and force them to be recompiled the next time each procedure is executed. This is a fairly drastic measure but can apply in some circumstances. For example, you might use it before and after running a nightly job that executes a number of procedures with atypical parameter values that usually run during the day with more typical parameters. This would help make sure that you don't reuse the plans from earlier in the day and that the plans created for your nightly job run aren't reused the next day.

Another situation in which you can run into trouble with parameter sniffing is when an execution plan in the cache reflects typical parameter values that you pass, but you need to pass an atypical parameter into a procedure and you need it to execute as quickly as possible. In this case, the problem isn't that you have a suboptimal plan in the cache—for the majority of your queries, the plan is optimal. The problem is that these two queries shouldn't be sharing an execution plan. Say, for example, that you have a stored procedure to which you pass a country code so that it can return national sales figures. Most of the time, you pass in “US” because your business is based in the United States and most of your sales are in that country. Due to the fact that U.S.-based sales records make up most of your sales table, the optimizer generates an execution plan that uses a table scan. A table scan is more efficient in this situation than an index seek because most of the rows are being returned anyway. Sometimes, however, you pass in a different country code—a code for a country for which there may be only a few sales. You expect this query to return relatively quickly based on the handful of rows it will eventually yield, but it doesn't. It also results in a table scan because it reuses the plan originally compiled when you passed in “US.” One potential solution would be to reorganize the procedure into multiple procedures—one for U.S.-based sales and one for all other countries. Given that your sales to countries outside the United States are relatively few and fairly evenly distributed, you will likely see an index seek (provided that an appropriate index exists) when querying for sales from these countries. Conversely, your queries for U.S.-based sales will continue to use table scans because that is the most efficient way to service them. Especially if a plan takes awhile to compile (and, hence, is not an ideal candidate for automatic recompilation with each execution), using multiple procedures in this manner may be a viable solution for you.