Question

O.Ragain on Wed, 17 Dec 2014 14:37:29


Hi,

So, yet again, I ran into weird happenings with execution plan. If I execute the following, it uses the index on the msg_conversation table.

SELECT
	case network_code
		when 'Async' then 'Telus B'
		when 'AsyncALL' then 'Telus A'
		else network_code
	end as network_code
	,count(1) as claims
FROM (
	select conv_start_dt, last_event_dt
		, case 
			when network_code in ('Async','AsyncALL') then network_code
			else 'Others'
		end as network_code
		, client_outcome_id, host_outcome_id
	from [iTRANS].[dbo].[msg_conversation] with ( nolock)
	right join network with(nolock) on comm_to = network_code	
	where  dateadd(hour,-24,getutcdate()) <  conv_start_dt
)as t
group by network_code with rollup

but if I do this:

SELECT
	case network_code
		when 'Async' then 'Telus B'
		when 'AsyncALL' then 'Telus A'
		else network_code
	end as network_code
	,count(1) as claims
FROM (
	select conv_start_dt, last_event_dt
		, case 
			when network_code in ('Async','AsyncALL') then network_code
			else 'Others'
		end as network_code
		, client_outcome_id, host_outcome_id
	from [iTRANS].[dbo].[msg_conversation] with ( nolock)
	right join network with(nolock) on comm_to = network_code	
	where  DATEADD(dd, 0, DATEDIFF(dd, 0, getutcdate())) <  conv_start_dt
)as t
group by network_code with rollup

where the only part that change is:

where  dateadd(hour,-24,getutcdate()) <  conv_start_dt

to:

where  DATEADD(dd, 0, DATEDIFF(dd, 0, getutcdate())) <  conv_start_dt

It runs a table scan.

If I put the dateadd thingy into a variable, same thing happens. the conv_start_dt is a datetime. getutcdate is a datetime too.

Any idea why the engine would revert to a table scan on the msg_conversation table in the second case but not the first ?

Thanks

Edit: If I force the use of the index, then it works fine. I am just wondering why it would act this way.

Sponsored



Replies

Erland Sommarskog on Wed, 17 Dec 2014 15:09:37


This sounds familiar. Run this command and try again:

DBCC TRACEON(4199, -1)

using this WHERE clause:

where  DATEADD(dd, 0, DATEDIFF(dd, 0, getutcdate())) <  conv_start_dt

If this does not help, please post the output of "SELECT @@version".

Trace flag 4199 causes you to opt in all optimizer fixes since SQL 2008 RTM. Optimizer fixes are not exposed by default (unless as the bug did not cause incorrect results), and I know that there was a bug where the optimizer computed datediff incorrectly.

O.Ragain on Wed, 17 Dec 2014 16:08:53


So no error. and the version is: Microsoft SQL Server 2005 - 9.00.4053.00 (X64)   May 26 2009 14:13:01   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2)

I know it is not supported anymore and we are in the process of migrating to 2012 and 2014. But I may end up having the same issue on 2012 / 2014.

So to temporarily fix it, I have forced the use of the index via the hints. Also, I am not 100% sure, but I would say that the query don't always execute with the same plan. Sometimes it uses the index, and sometimes it scans the table.

What did you think it could have been ?

Thanks for the help

Regards,


Gert-Jan Strik on Wed, 17 Dec 2014 19:54:55


So to temporarily fix it, I have forced the use of the index via the hints.

The alternative workaround would be to use both predicates...

Erland Sommarskog on Wed, 17 Dec 2014 22:48:53


Since you are on SQL 2005, I think the issue is simply that the optimizer is not able to evaluate the datediff expression at all, and therefore assumes a 30% hit rate, the default for an open range. And with a 30% hit rate, the index is more expensive to use than a table scan.

On the other hand, the optimizer in SQL 2005 was apparently able to compute dateadd at compile time, and therefore could get a better estimate.

You can test my hypothesis by looking look at the query plans, and particularly at the estimated row count for the table with the conv_start_dt column.

If memory serves, it was in SQL 2008, they added understanding of datediff. Unfortunately, they confused the arguments and assumed that the normal rules for subtraction applied. This was later fixed, but it is one of the fixes hidden by TF 4199. So you may need that flag once you are on SQL 2012/2014.

Samir Abrahao on Thu, 18 Dec 2014 11:32:47


The DATEDIFF function returns an INTEGER and the DATEADD function expects a datetime parameter, so a CONVERT_IMPLICIT operation is performed.

As these conversions are performed at runtime and not at compile time, the optimizer cannot properly estimate the output of the DATEADD function and can't decide if performing a b-tree seek is going to improve query performance, therefore a scan is choosen. 

You do realize you don't have to do that to round down the datetime parameter, right? All you have to do is to convert to the date type and it will work. You'll also get a better performance because you'll be replacing two scalar functions for a simple, explicit data conversion.

CONVERT(DATE, getutcdate())
Now, I've seen situations where TF 4199 would help when the parameter was being passed as a variable, especially on recursive CTE situations, and this flag is always full of surprises. If Sommarskog said there was a fix for datediff included, then it might work, but it is very important to understand why this happened and how you can avoid it in the future.

O.Ragain on Thu, 18 Dec 2014 12:54:58


if only DATE existed in 2005 :)

Even if I do the calculation out of the query, put it in a variable and then compare the variable to the conv_start_dt field it does the same thing. I tried.

Though I agree it is important to understand, I do have worse thing to fix first :)

Thanks a lot for the information and the time

Samir Abrahao on Thu, 18 Dec 2014 13:06:48


if only DATE existed in 2005 :)

Even if I do the calculation out of the query, put it in a variable and then compare the variable to the conv_start_dt field it does the same thing. I tried.

Though I agree it is important to understand, I do have worse thing to fix first :)

Thanks a lot for the information and the time


Neglected the fact you were working with 2005. Tested on 2012 and worked when implicit conversions are not present, whether the source of the value was a variable, a function or a literal value.

Just because there are clouds in the sky it doesn't mean it isn't blue. But someone will come and argue that in addition to clouds, birds, airplanes, pollution, sunsets, daltonism and nuclear bombs, all adding different colours to the sky, this is an undocumented behavior and should not be relied upon.


Erland Sommarskog on Thu, 18 Dec 2014 14:27:14


Even if I do the calculation out of the query, put it in a variable and then compare the variable to the conv_start_dt field it does the same thing. I tried.

Yes, with a variable you are completely without a chance. The optimizer has no knowledge of the variable value at compile-time and will therefore assume a hit rate of 30%.