SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SQL Server doing IMPLICIT_CONVERSION on datetime
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Bex
Aged Yak Warrior

United Kingdom
580 Posts

Posted - 01/25/2010 :  12:07:55  Show Profile  Send Bex an AOL message  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8768 Posts

Posted - 01/25/2010 :  12:15:40  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

Bex
Aged Yak Warrior

United Kingdom
580 Posts

Posted - 01/25/2010 :  12:22:57  Show Profile  Send Bex an AOL message  Reply with Quote
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
Go to Top of Page

Bex
Aged Yak Warrior

United Kingdom
580 Posts

Posted - 01/25/2010 :  12:24:14  Show Profile  Send Bex an AOL message  Reply with Quote
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
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8768 Posts

Posted - 01/25/2010 :  12:31:29  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 01/25/2010 :  14:47:35  Show Profile  Reply with Quote
I tried explicit cast, adding some data and UPDATE STATISTICS WITH FULLSCAN. No differences
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 01/25/2010 :  15:49:28  Show Profile  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 01/26/2010 :  02:31:07  Show Profile  Reply with Quote
I tried them all, in turn and together. It didn't make any difference.
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 01/26/2010 :  03:28:36  Show Profile  Reply with Quote
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
Go to Top of Page

Bex
Aged Yak Warrior

United Kingdom
580 Posts

Posted - 01/26/2010 :  03:34:40  Show Profile  Send Bex an AOL message  Reply with Quote
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
Go to Top of Page

Bex
Aged Yak Warrior

United Kingdom
580 Posts

Posted - 01/26/2010 :  03:36:19  Show Profile  Send Bex an AOL message  Reply with Quote
Hi lumbago

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

Thanks

Hearty head pats
Go to Top of Page

Bex
Aged Yak Warrior

United Kingdom
580 Posts

Posted - 01/26/2010 :  04:06:03  Show Profile  Send Bex an AOL message  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 01/26/2010 :  05:31:55  Show Profile  Reply with Quote
"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)
Go to Top of Page

Bex
Aged Yak Warrior

United Kingdom
580 Posts

Posted - 01/26/2010 :  05:53:24  Show Profile  Send Bex an AOL message  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 01/26/2010 :  06:08:27  Show Profile  Reply with Quote
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)
Go to Top of Page

Bex
Aged Yak Warrior

United Kingdom
580 Posts

Posted - 01/26/2010 :  06:31:20  Show Profile  Send Bex an AOL message  Reply with Quote
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
Go to Top of Page

Bex
Aged Yak Warrior

United Kingdom
580 Posts

Posted - 01/26/2010 :  06:33:59  Show Profile  Send Bex an AOL message  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 01/26/2010 :  07:00:46  Show Profile  Reply with Quote
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
Go to Top of Page

Bex
Aged Yak Warrior

United Kingdom
580 Posts

Posted - 01/26/2010 :  07:12:53  Show Profile  Send Bex an AOL message  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 01/26/2010 :  07:42:57  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 01/26/2010 07:43:27
Go to Top of Page

Bex
Aged Yak Warrior

United Kingdom
580 Posts

Posted - 01/26/2010 :  11:03:41  Show Profile  Send Bex an AOL message  Reply with Quote
Hi Peso

Here you go:

http://pastebin.com/m4a84ee78

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
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.23 seconds. Powered By: Snitz Forums 2000