为什么SQL Server不使用计算列上的索引？

Question

为什么SQL Server不使用计算列上的索引？

sql-serverperformancequery-optimizationsql-server-2014database-performance

16

在 SQL Server 2014 数据库中给出以下内容：

create table t 
(
    c1 int primary key,
    c2 datetime2(7),
    c3 nvarchar(20),
    c4 as cast(dbo.toTimeZone(c2, c3, 'UTC') as date) persisted
);

create index i on t (c4);

declare @i int = 0;

while @i < 10000 
begin
    insert into t (c1, c2, c3) values
        (@i, dateadd(day, @i, '1970-01-02 03:04:05:6'), 'Asia/Manila');
    set @i = @i + 1;
end;

toTimeZone是一个CLR UDF，用于将一个时区中的datetime2转换为另一个时区中的datetime2。

当我运行以下查询时：

select c1 
from t 
where c4 >= '1970-01-02'
    and c4 <= '1970-03-04';

SQL Server的执行计划表明，i没有被使用。相反，隐式索引上进行了扫描，然后进行了一些标量计算，最后使用查询的谓词进行过滤。我期望的执行计划是对i进行扫描。

请使用此ZIP文件中的SSDT项目尝试复制问题。它包括CLR UDF的模拟定义以及我得到的执行计划。

- Chry Cheng

这是索引计算列的明确要求清单：https://msdn.microsoft.com/zh-cn/library/ms189292.aspx 检查您的情况；很可能需要将计算列声明为“persisted”。 - Roger Wolf

1

只需在我的实例（2014，x64 Dev Edition）上运行即可看到索引查找。因此，您需要进一步优化重现此问题的确切方法。 - Damien_The_Unbeliever

1

但现在的问题是我们没有 to_time_zone，因此其他人能够在自己的系统上复制这个过程的机会现在为零。 - Damien_The_Unbeliever

这是我猜测为什么优化器在你的第一个测试中没有使用i索引。当你运行测试时，统计数据已经过时，优化器做出了错误的决定。如果在表填充数据之后重建索引（或创建索引），那么它的统计数据将是最新的，优化器会做出正确的决定。 - Vladimir Baranov

1

@AndrewO'Brien - 根据提供的项目，即使使用提示，我的2014版本也完全无法匹配它 https://i.stack.imgur.com/U1Dyr.png - Martin Smith

显示剩余5条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Martin Smith · Accepted Answer

我能够使用您提供的项目重现问题（可能与这里和连接项这里相同）。

计算列首先展开为底层表达式，然后可能会再次匹配到计算列。

您的计划中的过滤器显示它被展开为。

CONVERT(date,[computed-column-index-problem].[dbo].[toTimeZone](CONVERT_IMPLICIT(datetime,[computed-column-index-problem].[dbo].[t].[c2],0),CONVERT_IMPLICIT(nvarchar(max),[computed-column-index-problem].[dbo].[t].[c3],0),CONVERT_IMPLICIT(nvarchar(max),'UTC',0)),0)>=CONVERT_IMPLICIT(date,[@1],0) 
AND 
CONVERT(date,[computed-column-index-problem].[dbo].[toTimeZone](CONVERT_IMPLICIT(datetime,[computed-column-index-problem].[dbo].[t].[c2],0),CONVERT_IMPLICIT(nvarchar(max),[computed-column-index-problem].[dbo].[t].[c3],0),CONVERT_IMPLICIT(nvarchar(max),'UTC',0)),0)<=CONVERT_IMPLICIT(date,[@2],0)

这些对于 nvarchar(max) 的隐式转换似乎是造成问题的原因。一个不需要 CLR 的简单重现代码如下：

DROP TABLE IF EXISTS t 
DROP FUNCTION IF EXISTS [dbo].[toTimeZone]

GO

CREATE FUNCTION [dbo].[toTimeZone] (@newTimeZone [NVARCHAR](max))
RETURNS DATE
WITH schemabinding
AS
  BEGIN
      RETURN DATEFROMPARTS(1970, 01, 02)
  END

GO

CREATE TABLE t
  (
     c1 INT IDENTITY PRIMARY KEY,
     c4 AS dbo.toTimeZone(N'UTC') persisted
  );

CREATE INDEX i
  ON t (c4);

INSERT INTO t
DEFAULT VALUES

SELECT c1
FROM   t WITH (forceseek)
WHERE  c4 >= '1970-01-02'
       AND c4 <= '1970-03-04';

错误 8622，级别 16，状态 1，行 27：查询处理器无法生成查询计划，因为该查询中定义了提示。请重新提交查询，不要指定任何提示并且不要使用 SET FORCEPLAN。

如果我更改函数定义为

public static DateTime toTimeZone(DateTime dateTime,
    [SqlFacet(IsFixedLength=false, IsNullable=true, MaxSize=50)]
    string originalTimeZone,
    [SqlFacet(IsFixedLength=false, IsNullable=true, MaxSize=50)]
    string newTimeZone)
{
    return dateTime.AddHours(-8);
}

因此，字符串参数变为nvarchar(50)。这样就能匹配并进行查找。

具体来说，需要传递字面值UTC的是第二个参数。如果仅将注释应用于第一个参数，则即使使用with (forceseek)提示，计划也不会产生查找。如果仅将注释应用于第二个参数，则可以产生查找 - 尽管计划显示警告。