Between an operator generating a bad query plan using parameters

Question

I have a simple date table (Date, DateID) that contains a list of dates between January 1, 1900 and December 31, 2100.

When choosing from a table using the between operator and hard-coded parameter values, I get the correct query plan with 3 evaluated rows compared to two actual rows:

 select v.Date from Dates v where v.Date between '20130128' and '20130129'; 

However, when replacing hard-coded values ​​with parameters, the query plan changes to a very poor plan, with more than 6,000 evaluated rows and only 2 actual rows:

 select v.Date from Dates v where v.Date between @startdate and @enddate; 

Query plans are identical in themselves, it’s just the difference in the estimated lines, which leads to the start of a parameterized query about 4 times slower than a programmed query. Is there something that I am missing, why the parameterized version is much slower, and what indexes / hints can I give SQL Server to help it use the correct query plan?

Additional Information:

• The problem does not arise when using simple equality criteria = , it seems specific to the between operator.
• If I add option(recompile) at the end of a parameterized query, I get a perfect query plan identical to a hard-coded query.
• There are only two columns in the date table: Date and DateID with a clustered index in the DateID column of the primary key and a unique non-clustered index in the Date column. All updated statistics.
• The query plan performs automatic parameterization for the hard-coded query, replacing the hard-coded values ​​with @ 1 and @ 2 and displaying the query as uppercase. It does not seem to perform any conversions for the parameterized query.
• Using SQL Server 2008 R2.

I know enough to realize to suspect that this is some kind of problem with sniffing options. Why not add option(recompile) to the request? This is used as part of a much more complex query, and I understand that it is good practice to let SQL Server do its job and reuse query plans from the cache where possible.

Edit and update: thanks for the thoughtful answers. To clarify the question, the query plan uses an excellent index for both of the above queries, but why doesn’t he admit that the date range is only two days for a parameterized query, why does he think that the range is 6000 rows wide? Especially when, looking at the query plan, does SQL Server perform automatic parameterization for a hard-coded query? In the basic query plan, both plans look the same, because both of them are parameterized!

Answer 1

The query plan is based on the parameter values ​​when the query is first run. This is called the sniffing option . When you add option (recompile) , a new plan is created for each execution.

The query plan is cached based on the hash of the SQL query. So you have a different slot for both versions of your request.

Adding option (recompile) is a good solution. You can also use:

 option (optimize for (@startdate = '20130128', @enddate = '20130129')); 

To create a query plan as if these values ​​were passed.

For testing, you can remove all plans from the cache with:

 DBCC FREEPROCCACHE 

Answer 2

The problem is probably related to the sniffing parameter , a technique designed to optimize the query plan based on the parameters that you pass for the first time.

I had a bad experience with the sniffing parameter with the date parameters in the past. Obviously, there is a chance with the sniffing parameter that the values ​​originally entered for the request are not typical for the typical use of the request (as a result, the query plan is optimized for non-standard values), however with the date parameters, in particular, I often found that the resulting plan The query is actually very inefficient for all values. I have no idea why this, however, disables the sniffing option, usually fixing it.

You can prevent SQL Server from sniffing a parameter using OPTIMIZE FOR UNKNOWN (I never used this, so I can’t guarantee that it works) or, alternatively, copying parameters to local variables seems to prevent SQL Server from executing sniffing parameter

 -- Perform the query using @StartDateLocal and @EndDateLocal DECLARE @StartDateLocal DATETIME; SET @StartDateLocal = @StartDate; 

Disabling the sniffing parameter in this way is better than forcing the recompilation of the query plan each time, since compiling the query plan is usually relatively expensive compared to the cost of executing the request if the request is not too slow.