“之间"操作员在使用参数时生成错误的查询计划

2022-01-16 00:00:00 sql sql-server sql-server-2008-r2

我有一个简单的日期表 (Date, DateID)，其中包含 1900 年 1 月 1 日至 2100 年 12 月 31 日之间的日期列表.

I have a simple dates table (Date, DateID) which contains a list of dates between 1-Jan-1900 and 31-Dec-2100.

当使用 between 运算符和硬编码参数值从表中进行选择时，我得到了一个正确的查询计划，其中 3 个估计行与 2 个实际行相比:

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

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

但是，当用参数替换硬编码值时，查询计划变成了一个非常糟糕的计划，估计有超过 6000 行，而实际只有 2 行:

However when replacing the hard-coded values with parameters, the query plan changes to a very poor plan, with over 6000 estimated rows and only 2 actual rows:

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

查询计划本身是相同的，只是估计行数的差异导致参数化查询的运行速度比硬编码查询慢约 4 倍.关于为什么参数化版本运行速度如此之慢，以及我可以为 SQL Server 提供哪些索引/提示来帮助它使用正确的查询计划，我有什么遗漏吗?

The query plans themselves are identical, it's just the difference in estimated rows that is causing the parameterised query to run about 4 times slower than the hard-coded query. Is there anything I'm missing as to why the parameterised version runs so much slower, and what indexes/hints can I give SQL Server to help it use the correct query plan?

一些附加信息:

  • 使用简单相等 = 标准时不会出现问题，它似乎特定于 between 运算符.
  • 如果我将 option(recompile) 添加到参数化查询的末尾，我会得到一个完美的查询计划，与硬编码查询相同.
  • dates 表只有两列，Date 和 DateID，主键 DateID 列上有一个聚集索引，Date 列上有一个唯一的非聚集索引.都有更新的统计数据.
  • 查询计划对硬编码查询执行自动参数化，将硬编码值替换为@1 和@2，并将查询显示为大写.它似乎没有对参数化查询执行任何转换.
  • 使用 SQL Server 2008 R2.
  • The problem does not arise when using simple equality = criteria, it seems specific to the between operator.
  • If I add option(recompile) to the end of the parameterised query, I get a perfect query plan, identical to the hard-coded query.
  • The dates table has only two columns, Date and DateID, with a clustered index on the primary key DateID column, and a unique non-clustered index on the Date column. All have updated statistics.
  • The query plan performs automatic parameterisation for the hard-coded query, replacing the hard-coded values with @1 and @2, and displaying the query as uppercase. It doesn't appear to perform any transformation for the parameterised query.
  • Using SQL Server 2008 R2.

我足够了解意识到 怀疑这是某种参数嗅探问题.为什么不将 option(recompile) 添加到查询中?这被用作更大的复杂查询的一部分，我理解好的做法是让 SQL Server 做它的事情并在可能的情况下重用缓存中的查询计划.

I know enough to realise suspect that this is some sort of parameter sniffing problem. Why not add option(recompile) to the query? This is being used as part of a much larger complex query and I understand good practice is to let SQL Server do its thing and re-use query plans from the cache where possible.

编辑和更新:感谢您迄今为止的周到回复.为了进一步细化问题，查询计划对上述两个查询都使用了一个非常好的索引，但是为什么它不能识别参数化查询的日期范围只有两天宽，为什么它认为范围是 6000行宽?尤其是当查看查询计划时，SQL Server 无论如何都在为硬编码查询执行自动参数化?在底层查询计划中，两个计划看起来相同，因为它们都是参数化的！

Edit and update: thanks for the thoughtful responses so far. To refine the question further, the query plan uses a perfectly good index for both of the above queries, but why does it not recognise that the date range is only two days wide for the parameterised query, why instead does it think the range is 6000 rows wide? Especially when, looking at the query plan, SQL Server is performing automatic parameterisation for the hard-coded query anyway?? In the underlying query plan, both plans look identical in that they both are parameterised!

推荐答案

查询计划基于您首次运行查询时的参数值.这称为参数嗅探.添加选项(重新编译)时，每次执行都会生成一个新的计划.

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

查询计划基于 SQL 查询的哈希值进行缓存.因此，两个版本的查询都有不同的缓存槽.

A query plan is cached based on the hash of the SQL query. So there's a different cache slot for both versions of your query.

添加 option (recompile) 是一个很好的解决方案.你也可以使用:

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

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

生成一个查询计划，就好像这些值已被传入一样.

To generate a query plan as if those values had been passed in.

为了测试，您可以使用以下命令从缓存中删除所有计划:

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

DBCC FREEPROCCACHE

相关文章