Same databases on two different servers...I get different execution plans on each database...why?

1 Sep 2006 12:54 AM

mchi55

I have the exact same database on two different servers...but when I
run the same query against both of them...they have different execution
plans.

One of the queries returns in 1 second...where the other one returns in
4 seconds.

Is the normal? how can I get the slow server to run with the same
execution plan as the fast one?

I restored the database from the slow server to the fast server and ran
the queries right away...so I know nothing was changed and no the
indexes were rebuilt or anything.
Are the statistics somehow rebuilt when you restore a database? Could
that be it?

1 Sep 2006 1:30 AM

Stu

Execution plans rely on a number of variables that can be influenced by
hardware factors(when you say slower server, what do you mean?), usage
(statistics and cached plans), server-wide settings in SQL Server, and
(of course) database design. Since you're comparing identical
databases (in terms of both design and quantity of data), I'd start
looking at hardware differences. I'd be very surprised if any other
query (other than very simple ones) used the same execution plans on
different hardware.

You may want to take a look at
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx to
get a feel for some of the driving forces behind plan reuse.

Stu

mch***@yahoo.com wrote:
Show quote

> I have the exact same database on two different servers...but when I
> run the same query against both of them...they have different execution
> plans.
>
> One of the queries returns in 1 second...where the other one returns in
> 4 seconds.
>
> Is the normal? how can I get the slow server to run with the same
> execution plan as the fast one?
>
> I restored the database from the slow server to the fast server and ran
> the queries right away...so I know nothing was changed and no the
> indexes were rebuilt or anything.
> Are the statistics somehow rebuilt when you restore a database? Could
> that be it?

1 Sep 2006 2:39 AM

Uri Dimant

In addition to Stu's suggestions , i'd check SQL Server's comfiguration on
both servers. Some SET... setting statements might affect a performance.

Just before running the queries , run EXEC sp_updatestats for tables in
specific database

<mch***@yahoo.com> wrote in message
Show quote

news:1157072049.646487.233680@m79g2000cwm.googlegroups.com...
>I have the exact same database on two different servers...but when I
> run the same query against both of them...they have different execution
> plans.
>
> One of the queries returns in 1 second...where the other one returns in
> 4 seconds.
>
> Is the normal? how can I get the slow server to run with the same
> execution plan as the fast one?
>
> I restored the database from the slow server to the fast server and ran
> the queries right away...so I know nothing was changed and no the
> indexes were rebuilt or anything.
> Are the statistics somehow rebuilt when you restore a database? Could
> that be it?
>

1 Sep 2006 7:17 AM

Erland Sommarskog

(mch***@yahoo.com) writes:

> I have the exact same database on two different servers...but when I
> run the same query against both of them...they have different execution
> plans.
>
> One of the queries returns in 1 second...where the other one returns in
> 4 seconds.
>
> Is the normal? how can I get the slow server to run with the same
> execution plan as the fast one?
>
> I restored the database from the slow server to the fast server and ran
> the queries right away...so I know nothing was changed and no the
> indexes were rebuilt or anything.

Things that can matter is available memory, and most of all, of course, the
number of CPUs available. I also seem to recall that for parallel plans,
there is a decision taken at run-time on how many processors to actually
use depending on the load of the system.

The edition of SQL Server may also matter, some performance features are not
available in Standard Edition. Also check @@version to see if the version
is exactly the same.

Yet an issue that may matter is parameter sniffing. The plan on the
original server may have been in cache for a long time, and be based
the input parameters on its first run.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx