Same query, different execution plan and time | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Same query, different execution plan and time

Hello, We are having a problem at the office now where the same query has two different execution plans on two different databases running on the same server. On database is a backup from a week ago, the otherone is a snapshot of the production database as of today. The newer database has approximately 30% more data than the old one, both in the range of the hundred of thousands of records. In the old database, the query completes in 5 minutes, which is is fine given the complexity of the query (which uses a nested query involving a view, etc), on the newer database, it takes more than 16 hours (we don’t know, we haven’t waited til it finishes). Our DBA says that the execution plan is both databases. Both are running on the same server, which is a dual pentium 1GHz with 1 GB ram. Some people are blaiming it on the volume of data, can it be? The difference is just about 30%, and the difference in time is just injcredible! Any suggestion where to look at? Thanks, Luis
Did you try updating the statistics and re-compiling the paln? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
Hello Gaurav, Thanks for your quick reply. T am not the DBA, just a developer, and at the offices the roles are well defined, so the DBAs are reponsible for all these things. This query has been working for about a months already with no problems. One day they called with this time out I described, but the DBA continues to blame it on the data, which is unacceptable by the client, since they expect their volume of data to grow continuosly, month after month. Is it possible that a 30% increase in data triggers such an incredible execution time and changes the execution plan? In the old database, just from a week ago, it executes as it should. I’m offsite now so I do not have access to the server, but since this is an emergency situation, and the DBAs haven’t found the cause of the problem or the solution, I stepped in to se if I could help. I’ll email them tonight your suggestion to see if this helps. I’ll post the results. I also failed to specify that we are talking about MSFT SQL Server 2000 with latest SP applied. Any other suggestion or explanation why this dramatic difference? Thanks for your help. Luis
quote:Originally posted by gaurav_bindlish Did you try updating the statistics and re-compiling the paln? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

If the query is running without any issues from ages, then ensure to obtain the UPDATE STATS and recompiling the SPs involved as suggested by Gaurav. Books online is the best resources for further information.
Refer the DBAs about this site which has extensive information about improving PERFORMANCe on SQL Server. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I’ll sure do! Thanks you all. Luis
quote:Originally posted by satya If the query is running without any issues from ages, then ensure to obtain the UPDATE STATS and recompiling the SPs involved as suggested by Gaurav. Books online is the best resources for further information.
Refer the DBAs about this site which has extensive information about improving PERFORMANCe on SQL Server. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

]]>