SQL Server doing IMPLICIT_CONVERSION on datetime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Server doing IMPLICIT_CONVERSION on datetime

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-01-25 : 12:07:55

I have 2 databases that have different table schemas. However, both DBs have a partitionDate attribute on all tables, and each table is partitioned by this date. The tables are concatenated using a view.

Both DBs have CHECK constraints on PartitionDate so that the view is updateable, and also accesses only the tables required when specifying a date range in a SELECT query.

However, the 1st database behaves as expected when issued with the following statement:

SELECT * FROM dbo.Item WHERE PartitionDate = '20101020'

However, the 2nd DB scans all tables in the view. When I looked at the execution plan, I noticed that an IMPLICIT_CONVERSION is being performed on the date:

|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2009-12-10 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2009-12-11 00:00:00.000')))

Is it this conversion that is causing the scanning of all tables? And why is this behaviour happening in one DB and not the other? Both have different table schemas, but both are following the same pattern (partitioned on PartitionDate and check constraint on the PartitionDate field to restrict only one days worth of data).

How can I prevent it performing an IMPLICIT_CONVERT?

Thanks

Hearty head pats

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-25 : 12:15:40

Looks like the IMPLICIT_CONVERT is done on the given parameter and not on the column in the table so there should not be the problem.

No, you're never too old to Yak'n'Roll if you're too young to die.

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-01-25 : 12:22:57

I've discovered something interesting......

One database is on SQL Server 2005 and the other (the one with the issue) is on 2008. I scripted out the one on 2005 and deployed to 2008, to ensure it was the same on 2005 and 2008. It was. However, when I ran the following code on both 2005 and then 2008, 2008 performs an implicit convert, whereas 2005 does not:

create database test
go
use test
go
create table dbo.Test_20100124
(id int
, DateTest datetime
)
alter table dbo.Test_20100124 add constraint CK_Test_20100124 check (DateTest >= '20100124' AND DateTest < '20100125')
go
create table dbo.Test_20100125
(id int
, DateTest datetime
)
alter table dbo.Test_20100125 add constraint CK_Test_20100125 check (DateTest >= '20100125' AND DateTest < '20100126')
go
create view vw_Test
as
select * from dbo.Test_20100124
union all
select * from dbo.Test_20100125
go
set Showplan_all on
go
select * from vw_Test where DateTest = '2010-01-24'
go
drop database test
go

Is this something to do with the new date datatypes introduced in 2008?

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-01-25 : 12:24:14

Sorry, forgot the output:

2005:

|--Compute Scalar(DEFINE:([test].[dbo].[Test_20100124].[id]=[test].[dbo].[Test_20100124].[id], [test].[dbo].[Test_20100124].[DateTest]=[test].[dbo].[Test_20100124].[DateTest]))
|--Table Scan(OBJECT:([test].[dbo].[Test_20100124]), WHERE:([test].[dbo].[Test_20100124].[DateTest]='2010-01-24 00:00:00.000'))

2008:

|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2010-01-24 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2010-01-25 00:00:00.000')))
| |--Table Scan(OBJECT:([BEX_DI_CRDM].[dbo].[Test_20100124]), WHERE:([BEX_DI_CRDM].[dbo].[Test_20100124].[DateTest]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2010-01-25 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2010-01-26 00:00:00.000')))
|--Table Scan(OBJECT:([BEX_DI_CRDM].[dbo].[Test_20100125]), WHERE:([BEX_DI_CRDM].[dbo].[Test_20100125].[DateTest]=CONVERT_IMPLICIT(datetime,[@0],0)))

Hearty head pats

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-25 : 12:31:29

Maybe that has to do with the new datatype DATE in 2008...

But I don't believe that it hits the performance.

I hope someone else with a bit more experience can enlighten.

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-01-25 : 14:47:35

I tried explicit cast, adding some data and UPDATE STATISTICS WITH FULLSCAN. No differences

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-01-25 : 15:49:28

quote:
Originally posted by Kristen

I tried explicit cast, adding some data and UPDATE STATISTICS WITH FULLSCAN. No differences

What did you cast the Query or the Constraint or both? If you didn't try putting the explicit conversion on the constraint, I'd try that. I'm in a stgrange spot right now and I only have a 2005 DB to "play" with.

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 02:31:07

I tried them all, in turn and together. It didn't make any difference.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-01-26 : 03:28:36

I created two databases on the same machine only setting the compatibility level to 90 on one of them and the execution plans are 100% identical for the select in both databases (with no implicit conversion). Do you have different regional settings or something on these servers? Something else that's different...?

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-01-26 : 03:34:40

Hey Guys

Thank you all for your help. I'm going to continue playing with it today to see if I can shed some light.

The wierd thing, when I scripted out the database on 2005, and deployed it to 2008, it behaved as expected (only went to the relevant partition when using a date filter). So there may be some option to specify behaviour or backward compatible behaviour?!?!??!?

I'll post an update later today!

Bex

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-01-26 : 03:36:19

Hi lumbago

Very possibly, I'll look into that now and get back to you.

Thanks

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-01-26 : 04:06:03

Ooooooooookay.......

I've just run exactly the same code that I posted yesterday, without making any changes to the DB server (as I was the last out and first in), and now I'm getting the results that I wanted?!?!?!?!?!!?!? Confused?????? Yes I am!

select * from vw_Test where DateTest = '20100124'
|--Compute Scalar(DEFINE:([Union1008]=[test1].[dbo].[Test_20100124].[id], [Union1009]=[test1].[dbo].[Test_20100124].[DateTest]))
|--Table Scan(OBJECT:([test1].[dbo].[Test_20100124]), WHERE:([test1].[dbo].[Test_20100124].[DateTest]='2010-01-24 00:00:00.000'))

I can't get on the actual box at the moment (will have to wait for someone to come in and grant me access), but could there have been any updates that occurred last night for 2008?

I'm happy that it now works as expected, but I'm not happy that I cannot diagnose what the issue was in the first place.....

Thanks again for all your help, and if anyone has any idea why this was occurring and now is fixed would be greatly appreciated :)

Bex

Hearty head pats

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 05:31:55

"now I'm getting the results that I wante"

Stats changed in the meantime?

My test was using your empty-table example (I did insert one row and Update Stats, but that didn't change anything)

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-01-26 : 05:53:24

Hi Kristen

Ok, I take back what I said. This morning, when running the code to create the test DB (in previous post), only the relevant tables were being accessed.... JOY. Whereas yesterday, I was experiencing all tables being scanned. I didn't change it in any way. This code is still producing the following results now:

select * from vw_Test where DateTest = '20100124'
|--Compute Scalar(DEFINE:([Union1008]=[test].[dbo].[Test_20100124].[id], [Union1009]=[test].[dbo].[Test_20100124].[DateTest]))
|--Table Scan(OBJECT:([test].[dbo].[Test_20100124]), WHERE:([test].[dbo].[Test_20100124].[DateTest]='2010-01-24 00:00:00.000'))

When I ran the a query against the 2008 DB, again, only the relevant tables were being accessed. However, in the last few hours, something has changed, and the 2008 DB is now scanning ALL the tables again.

I've been assurred that nothing has changed. I have no idea WHAT is going on...........

Hearty head pats

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 06:08:27

Is there anything in sys.sysindexes that you could monitor to see when stats are rebuilt? (I'm thinking of [rowmodctr] which gives a clue tow hen an update when have benefit)

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-01-26 : 06:31:20

Hi Kristen

I think I am beginning to get somewhere. It seems when I execute a query within a connection for the 2008 DB, all tables are scanned. If I execute the same query in a connection for another DB, and fully qualify the DB name, only the relevant tables are accessed:

USE [master]
GO
select * from BEX_DI_CRDM.dbo.CRDM_ItemControl
WHERE DateReceived = '20100120'
GO

Results:

select * from BEX_DI_CRDM.dbo.CRDM_ItemControl  WHERE DateReceived = '20100120'
  |--Compute Scalar(DEFINE:([Union1126]=[BEX_DI_CRDM].[crdm].[ItemControl_20100120].[ID], [Union1127]=[BEX_DI_CRDM].[crdm].[ItemControl_20100120].[DateReceived], [Union1128]=[BEX_DI_CRDM].[crdm].[ItemControl_20100120].[Item], [Union1129]=[BEX_DI_CRDM].[crdm].[ItemControl_20100120].[Reason], [Union1130]=[BEX_DI_CRDM].[crdm].[ItemControl_20100120].[SourceSystem], [Union1131]=[BEX_DI_CRDM].[crdm].[ItemControl_20100120].[OtherSystem]))
       |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20100120].[PK_ItemControl_20100120]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20100120].[DateReceived]='2010-01-20 00:00:00.000'))

USE [BEX_DI_CRDM]
GO
select * from BEX_DI_CRDM.dbo.CRDM_ItemControl
WHERE DateReceived = '20100120'

Results:

select * from BEX_DI_CRDM.dbo.CRDM_ItemControl WHERE DateReceived = '20100120'
|--Concatenation
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2009-12-10 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2009-12-11 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20091210].[PK_ItemControl_20091210]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20091210].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2009-12-11 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2009-12-12 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20091211].[PK_ItemControl_20091211]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20091211].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2009-12-12 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2009-12-13 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20091212].[PK_ItemControl_20091212]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20091212].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2009-12-13 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2009-12-14 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20091213].[PK_ItemControl_20091213]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20091213].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2009-12-14 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2009-12-15 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20091214].[PK_ItemControl_20091214]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20091214].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2009-12-15 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2009-12-16 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20091215].[PK_ItemControl_20091215]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20091215].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2009-12-16 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2009-12-17 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20091216].[PK_ItemControl_20091216]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20091216].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2009-12-17 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2009-12-18 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20091217].[PK_ItemControl_20091217]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20091217].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2009-12-18 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2009-12-19 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20091218].[PK_ItemControl_20091218]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20091218].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2009-12-19 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2009-12-20 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20091219].[PK_ItemControl_20091219]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20091219].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2009-12-20 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2009-12-21 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20091220].[PK_ItemControl_20091220]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20091220].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2009-12-21 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2009-12-22 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20091221].[PK_ItemControl_20091221]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20091221].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2009-12-22 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2009-12-23 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20091222].[PK_ItemControl_20091222]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20091222].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2009-12-23 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2009-12-24 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20091223].[PK_ItemControl_20091223]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20091223].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2009-12-24 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2009-12-25 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20091224].[PK_ItemControl_20091224]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20091224].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2009-12-25 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2009-12-26 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20091225].[PK_ItemControl_20091225]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20091225].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2009-12-26 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2009-12-27 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20091226].[PK_ItemControl_20091226]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20091226].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2009-12-27 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2009-12-28 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20091227].[PK_ItemControl_20091227]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20091227].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2009-12-28 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2009-12-29 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20091228].[PK_ItemControl_20091228]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20091228].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2009-12-29 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2009-12-30 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20091229].[PK_ItemControl_20091229]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20091229].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2009-12-30 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2009-12-31 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20091230].[PK_ItemControl_20091230]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20091230].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2009-12-31 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2010-01-01 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20091231].[PK_ItemControl_20091231]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20091231].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2010-01-01 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2010-01-02 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20100101].[PK_ItemControl_20100101]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20100101].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2010-01-02 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2010-01-03 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20100102].[PK_ItemControl_20100102]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20100102].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2010-01-03 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2010-01-04 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20100103].[PK_ItemControl_20100103]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20100103].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2010-01-04 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2010-01-05 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20100104].[PK_ItemControl_20100104]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20100104].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2010-01-05 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2010-01-06 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20100105].[PK_ItemControl_20100105]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20100105].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2010-01-06 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2010-01-07 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20100106].[PK_ItemControl_20100106]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20100106].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2010-01-07 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2010-01-08 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20100107].[PK_ItemControl_20100107]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20100107].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2010-01-08 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2010-01-09 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20100108].[PK_ItemControl_20100108]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20100108].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2010-01-09 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2010-01-10 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20100109].[PK_ItemControl_20100109]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20100109].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2010-01-10 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2010-01-11 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20100110].[PK_ItemControl_20100110]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20100110].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2010-01-11 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2010-01-12 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20100111].[PK_ItemControl_20100111]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20100111].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2010-01-12 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2010-01-13 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20100112].[PK_ItemControl_20100112]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20100112].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2010-01-13 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2010-01-14 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20100113].[PK_ItemControl_20100113]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20100113].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2010-01-14 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2010-01-15 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20100114].[PK_ItemControl_20100114]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20100114].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2010-01-15 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2010-01-16 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20100115].[PK_ItemControl_20100115]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20100115].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2010-01-16 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2010-01-17 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20100116].[PK_ItemControl_20100116]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20100116].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2010-01-17 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2010-01-18 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20100117].[PK_ItemControl_20100117]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20100117].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2010-01-18 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2010-01-19 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20100118].[PK_ItemControl_20100118]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20100118].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2010-01-19 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2010-01-20 00:00:00.000')))
| |--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20100119].[PK_ItemControl_20100119]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20100119].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))
|--Filter(WHERE:(STARTUP EXPR(CONVERT_IMPLICIT(datetime,[@0],0)>='2010-01-20 00:00:00.000' AND CONVERT_IMPLICIT(datetime,[@0],0)<'2010-01-21 00:00:00.000')))
|--Clustered Index Scan(OBJECT:([BEX_DI_CRDM].[crdm].[ItemControl_20100120].[PK_ItemControl_20100120]), WHERE:([BEX_DI_CRDM].[crdm].[ItemControl_20100120].[DateReceived]=CONVERT_IMPLICIT(datetime,[@0],0)))

I still can't seem to mimic the behaviour for the test DB however....

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-01-26 : 06:33:59

I've also tried changing the compatibility levels to both 90 and 100 and I still get the same results.....

alter database BEX_DI_CRDM
SET COMPATIBILITY_LEVEL = 100
GO

alter database BEX_DI_CRDM
SET COMPATIBILITY_LEVEL = 90
GO

Hearty head pats

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 07:00:46

Should be an Index Seek (on the Date index) rather than a Scan on the PK, for the first example, shouldn't it?

The second use-case looks like a candidate for a Service Pack fix to me

Not so hearty cow pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-01-26 : 07:12:53

Hi Kristen

You're absolutely right. The first should be a SEEK. I've 2 versions of the DB as I was playing with the PKs..... so the on the original it does do a SEEK.

A colleague is applying SP1 to the server, so hopefully, we'll see a difference.........

Not so hearty cow pats indeed

I'll post when we've done the update. Fingers, legs, arms etc crossed!

Hearty head pats

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-26 : 07:42:57

Can you post a link to a downloadable XML execution plan?
Even if they are "scanned" in the plan, the execution count may still be 0.

N 56°04'39.26"
E 12°55'05.63"

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-01-26 : 11:03:41

Hi Peso

Here you go:

[url]http://pastebin.com/m4a84ee78[/url]

I also ran a profiler, and although reads and rowcounts were the same, CPU was higher as well as duration for the query scanning all tables.

Thanks
Bex

Hearty head pats

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources