SQL Server 2005 缓存了一个永远无法工作的执行计划 [英] SQL Server 2005 cached an execution plan that could never work

问题描述

我们有一个视图，用于通过聚集索引在表中查找记录.该视图在 select 语句中还有几个子查询，它们在两个大表中查找数据，同样通过聚集索引.

We have a view that is used to lookup a record in a table by clustered index. The view also has a couple of subqueries in the select statement that lookup data in two large tables, also by clustered index.

为了极大地简化，它会是这样的:

To hugely simplify it would be something like this:

SELECT a,
(SELECT b FROM tableB where tableB.a=tableA.a) as b
(SELECT c FROM tableC where tableC.a=tableA.a) as c
FROM tableA

对 [tableB] 的大多数查找都正确地使用了 [tableB] 上的非聚集索引，并且工作效率很高.但是，SQL Server 偶尔会在生成执行计划时使用 [tableB] 上的索引，该索引不包含正在传递的值.因此，按照上面的示例，尽管 tableB 上存在列 [a] 的索引，但该计划会扫描具有列 [z] 的聚集索引.使用 SQL 自己的语言，计划的谓词与对象无关".我不明白为什么这会实用.结果，当 SQL 执行此操作时，它必须扫描索引中的每条记录，因为它永远不会存在，最多需要 30 秒.这似乎总是错误的.

Most lookups to [tableB] correctly use a non-clustered index on [tableB] and work very efficiently. However, very occasionally SQL Server, in generating an execution plan, has instead used an index on [tableB] that doesn't contain the value being passed through. So, following the example above, although an index of column [a] exists on tableB, the plan instead does a scan of a clustered index that has column [z]. Using SQL's own language the plan's "predicate is not relevant to the object". I can't see why this would ever be practical. As a result, when SQL does this, it has to scan every record in the index, because it would never exist, taking up to 30 seconds. It just seems plain wrong, always.

以前有没有人见过这种情况，执行计划做了一些看起来永远不可能正确的事情?无论如何我都会重写查询，所以我关心的不是查询的结构，而是更多关于为什么 SQL 会出错的问题.

Has any one seen this before, where an execution plan does something that looks like it could never be right? I am going to rewrite the query anyway, so my concern is less about the structure of the query, but more as to why SQL would ever get it that wrong.

我知道有时 SQL Server 可以选择一个运行一次的计划，随着数据集的变化它可能会变得效率低下，但在这种情况下它永远无法运行.

I know sometimes SQL Server can choose a plan that worked once and it can become inefficient as the dataset changes but in this case it could never work.

更多信息

• [tableB] 有 400 万条记录，[a] 的大部分值为 null
• 我现在无法掌握生成计划的初始查询
• 这些查询是通过 Coldfusion 运行的，但目前我对在 SQL Server 中独立看到过这个的人很感兴趣

推荐答案

这似乎总是错误的.

It just seems plain wrong, always.

您可能对 编程的第一条规则.

所以，按照上面的例子，虽然列 [a] 的索引存在于 tableB 上，计划改为扫描聚集索引有 [z] 列.

So, following the example above, although an index of column [a] exists on tableB, the plan instead does a scan of a clustered index that has column [z].

聚集索引总是包含所有行.它可能按 z 排序，但它仍将包含叶级别的所有其他列.

A clustered index always includes all rows. It might be ordered by z, but it will still contain all other columns at the leaf level.

SQL Server 有时更喜欢群集扫描而不是索引查找的原因是这个.当您进行索引查找时，您必须通过对聚集索引的书签查找来跟踪它以检索不在索引中的列.

The reason SQL Server sometimes prefers a clustered scan over an index seek is this. When you do an index seek, you have to follow it up with a bookmark lookup to the clustered index to retrieve columns that are not in the index.

当您进行聚集索引扫描时，您根据定义查找所有列.这意味着不需要书签查找.

When you do a clustered index scan, you by definition find all columns. That means no bookmark lookup is required.

当 SQL Server 需要很多行时，它会尝试避免书签查找.这是一个经过时间考验的选择.非聚集索引搜索通常会被聚集索引扫描击败.

When SQL Server expects many rows, it tries to avoid the bookmark lookups. This is a time-tested choice. Nonclustered index seeks are routinely beaten by clustered index scans.

您可以通过使用 with (index(IX_YourIndex)) 查询提示.

You can test this for your case by forcing either with the with (index(IX_YourIndex)) query hint.

这篇关于SQL Server 2005 缓存了一个永远无法工作的执行计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！