Why doesn't SQL Server use the index on the computed column?

Question

Given the following in a SQL Server 2014 DB:

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 is a CLR UDF that converts a datetime2 in a time zone to a datetime2 in another time zone.

When I run the following query:

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

The execution plan followed by SQL Server indicates that i isn't used.

Instead there is a scan on the implicit index on the PK followed by a couple of scalar computations before finally a filter using the predicates of the query. The execution plan that I was expecting is a scan on i.

Use the SSDT project in this ZIP file to try and replicate the problem. It includes a mock definition of the CLR UDF. Included also is the execution plan I get.

Deandra

Here is a definitive list of requirements for indexing computed columns: msdn.microsoft.com/en-us/library/ms189292.aspx Check your situation; most probably, you will need to declare the computed column as persisted. – Assegai

Just run on my instance (2014, x64 Dev Edition) and I'm seeing an index seek. So you need to refine further the exact way to reproduce this issue. – Indices

Strange. I ran the query again and this time there is now an index seek on i. – Deandra

I thought I could come up with a generic example that would still demo my problem. I changed the given in the question to make it closer to my actual case. – Deandra

But the problem now is that we don't have to_time_zone so the chances of anyone else being able to reproduce this on their own systems is now zero. – Indices

I provided a link to an SSDT project that can be used to set up a DB where the problem can be replicated. Also included the execution plan I get. – Deandra

Here is my guess why the optimizer didn't use index on i in your first test. The statistics were way out of date when you ran your test and optimizer made a wrong decision. If you rebuild an index (or create an index) after the table is populated with data, then its statistics would be up-to-date and optmizer would make a correct decision. – Packard

I ran update statistics t in the updated test. Execution plan stayed the same. – Deandra

Just curious, are you trying to determine why it didn't perform an index seek, or how to force it to? A forceseek will overwrite the optimizer. Trying to understand why the optimizer did what it did may be a rabit hole since it is based on local statistics. – Slipper

@AndrewO'Brien - with the project supplied at least my version of 2014 can't match it at all even with the hint i.sstatic.net/U1Dyr.png – Acidulent

Answer 1

I was able to reproduce the issue using your attached project (it's probably the same issue as here with connect item here)

computed columns are first expanded out to the underlying expression and then may or may not be matched back to the computed column later.

The filter in your plan shows that it gets expanded out to

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)

These implicit casts to nvarchar(max) appear to be doing the damage. A simple repro that does not require CLR is

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'; 

Msg 8622, Level 16, State 1, Line 27 Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

If I change the function definition to

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);
}

So the string parameters become nvarchar(50). Then it is able to match and give a seek

Specifically it is the second parameter that is being passed the literal UTC that requires this. If the annotation is applied to only the first parameter then the plan will not produce a seek even with the with (forceseek) hint. If the annotation is applied to only the second parameter then it can produce a seek - though the plan shows a warning.

Acidulent

Wow. You managed to not only reproduce the problem, but got to the bottom of it and found the solution. – Packard

@ Vladimir Baranov what is the solution or fix or workaround for this. – Valentinvalentina

I created PK based on identity column a computed persisted column as ('P'+ RIGHT('000000000'+CONVERT([VARCHAR](8),[ID],(0)),(7))) PERSISTED. I created both tables, in the same way, a persisted columns. When I tried to join both persisted columns on inner join , it does not pick the index and warning is being shown "Type conversion expression convert(varchar(8),id,0) may effect cardinality estimate in version Microsoft sql server 2016 -sp1cu4" – Valentinvalentina