468,103 Members | 1,255 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,103 developers. It's quick & easy.

Different execution plans - same data, same server

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???
Jul 23 '05 #1
3 2230
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???

Jul 23 '05 #2
"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.

Jul 23 '05 #3
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?

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by manning_news | last post: by
2 posts views Thread by Ina Schmitz | last post: by
1 post views Thread by Solo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.