August 20, 2002 at 7:16 am
Source database is setup correctly with indexes on unique fields.
Database is scripted from within enterprise manager - including indexes
A new db is created, and the script run against it
The data is directly copied using DTS
When queries are now run, indexes do not apper to be used correctly.
Running the same select query on the original and the copied db's results in the original db using an index seek, and the copied db using an index scan.
Indexes and stats have been rebuilt, and the indexes have been dropped and recreated - but the query still does a scan rather than a seek on the copied system.
Has anyone come across this problem? or no how to prevent it from occuring???
Cheers
Dan.
August 20, 2002 at 8:59 am
Have not. Same server?
Andy
August 20, 2002 at 9:02 am
No this is running on 2 diff servers.
Both running SP2
August 20, 2002 at 10:03 am
Same # of processors? Memory? Might want to actually copy the mdf over and attach it, see if that affects the query plan.
Andy
August 20, 2002 at 10:48 am
Tried it on same server - still had the problem
did a backup and restore to new db on same server - and the index worked correctly...
I'm confused 🙁
another strangeness:
if I run (on the affected system):
select fieldname from tablename where fieldname = 'value'
it does an index scan
if I run:
select fieldname from tablename where fieldname like 'value'
it uses the index...
August 20, 2002 at 10:51 am
Only thing I can think of...wild guess...is that because you're copying the data in it's arrange differently? Shouldnt matter, but maybe?
Andy
August 20, 2002 at 10:58 am
Maybe...
It appears to be a bug as far as I can see...
Seeing as the table has ~ .5 M records - the difference in io speed is considerable...
Yes the data may be arranged differently - but the indexes and stas have been rebuilt - so query optimizer should be able to do its job and work out that it should use the method which is 150 times quicker at retrieving the data...
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply