What is Parameter Sniffing? First of all, don't be scared about parameter sniffing. It's an expected behavior.
When a stored procedure is compiled or recompiled, the parameter values passed for that invocation are "sniffed" and used for cardinality estimation. The net effect is that the plan is optimized as if those specific parameter values were used as literals in the query. Take the following stored procedure as an example: create procedure dbo.SearchProducts @Keyword varchar(100) asselect * from Products where Keyword like @Keyword
Assume the table is has approximately 100,000 rows, and has a single-column nonclustered index on the Keyword column.
Let's say you call this the first time and pass in a parameter @Keyword='XBOX%'. Suppose the number of rows in the table with a keyword starting with XBOX is very small -- perhaps just a few dozen rows. The optimizer might choose to use a query plan that uses the index on the keyword column to evaluate the LIKE, then a bookmark lookup to retrieve the other columns for the row. This index seek + bookmark lookup plan will be cached and reused for subsequent executions of the procedure.
Performance Problems Caused by Parameter Sniffing However, at some point in the future the server must compile/recompile a new plan for the stored procedure (the prior plan may have been aged out of cache or auto update statistics kicked in on the Products table, etc). Unfortunately, the particular execution of the procedure that compiled the new plan had a @Keyword parameter of 'KINECT%'. Suppose that the filter 'KINECT%' returns 10% of the rows in the table. When compiling the procedure with this parameter, SQL might select a query plan that uses a full table scan. That plan would be ideal for the parameter 'KINECT%', but would be a terrible plan for other, more selective, search criteria. Unfortunately, following the recompile, the table scan plan would also get cached and reused. The performance of subsequent executions with more typical parameter values would suffer. Parameter sniffing allows SQL to compile a plan that is tailored to the type of parameter that is actually passed into the stored procedure. Generally speaking, this feature allows more efficient stored procedure execution plans, but a key requirement for everything to work as expected is that the parameter values used for compilation be "typical". Unfortunately, as illustrated in this hypothetical example, a procedure or parameterized query may occasionally be executed with an atypical parameter (data skew is often at play in these cases). Parameter sniffing performance problems can affect all sorts of queries, but queries that use LIKE (like the example described above) are especially prone to this class of problem. Performance problems caused by parameter sniffing are generally considered to be By Design.
Since SQL Server 2005 we have a new functionality that permits individual plans recompilation instead of compiling entire execution plan for single store procedure. You could find below some workaround when you are affecting by a performance issue.
1. Using dummy variables that are not directly displayed on parameters also ensure execution plan stability without need to add recompile hint, example below:
create procedure dbo.SearchProducts @Keyword varchar(100)AsDeclare @Keyworddummy as varchar(100)Set @Keyworddummy = @Keywordselect * from Products where Keyword like @Keyworddummy
2. To prevent this and other similar situations, you can use the following query option:
3. Disable auto-update statistics during the batch
Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |
Problem description: Last week, I got the error below inside BO.
Error: 8623, Severity: 16, State: 1.The query processor ran out of internal resources and could not produce a query plan.
Cause: This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. In my case, my customer has been getting the following error when attempting to select records through a query with a large number of entries in the "IN" clause (> 10,000).
Resolution: Our recommendation is to simplify the query. You may try divide and conquer approach to get part of the query working (as temp table) and then add extra joins / conditions.
See also remarks in the BOL (http://technet.microsoft.com/en-us/library/ms177682.aspx)“Including an extremely large number of values (many thousands) in an IN clause can consume resources and return errors 8623 or 8632. To work around this problem, store the items in the IN list in a table. The large IN clause needs to be changed to a table. “
Others workarounds: You could try to run the query using the hint option (force order), option (hash join), option (merge join), option (querytraceon 4102) with a plan guide. By enabling the traceflag 4102, we will revert the behavior to SQL Server 2000 for handling semi-joins.
Interesting KB:- FIX: A non-yielding scheduler error or an error 8623 occurs when you run a query that contains a large IN clause in SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2- FIX: Error message when you run a complex query after you install Cumulative Update 3 or Cumulative Update 4 for SQL Server 2005 Service Pack 2: "The query processor ran out of internal resources and could not produce a query plan"