Different execution plans - same data, same server

[James Walker]

Hi there - hoping someone can help me here!

I have a database that has been underperforming on a number of queries
recently - in a test environment they take only a few seconds, but on
the live data they take up to a minute or so to run. This is using the
same data.

Every evening a copy of the live data is copied to a backup 'snapshot'
database on the same server and also, on this copy the queries only
take a second or so to run. (This is testing through the Query
Analyser)

I've studied the execution plans for the same query on the snapshot db
and the live db and they seem to be significantly different - why is
this? it's looking at the same data and exactly the same code!!

Anybody got any ideas???

[Dan Guzman]

Was the 'snapshot' created with BACKUP/RESTORE or some other ETL technique?
If not BACKUP/RESTORE, it may be that statistics are different, resulting in
different plans. You might try updating stats on your live database

--
Hope this helps.

Dan Guzman
SQL Server MVP

"James Walker" <ja***@jimw.co.uk> wrote in message
news:82**************************@posting.google.c om...

Hi there - hoping someone can help me here!

I have a database that has been underperforming on a number of queries
recently - in a test environment they take only a few seconds, but on
the live data they take up to a minute or so to run. This is using the
same data.

Every evening a copy of the live data is copied to a backup 'snapshot'
database on the same server and also, on this copy the queries only
take a second or so to run. (This is testing through the Query
Analyser)

I've studied the execution plans for the same query on the snapshot db
and the live db and they seem to be significantly different - why is
this? it's looking at the same data and exactly the same code!!

Anybody got any ideas???

[fprose]

"Copy of the Live Data" - as in backup/restore? I'd update statistics
on both - then check the execution plan. I've seen the "wrong" plan
taken becuase statistics were up to date - opposite of what you would
expect.

[djwhisky]

Thanks for all your help people - i did:

UPDATE STATISTICS tablename

for all the dependent tables involved and its now fixed it and is
working nice and fast again...

one question still however, is why i've needed to do this?? Shouldn't
it automatically keep things like this up to date?