Same Query but different Execution plan.

I have two server Production and Development. Both are idenditical in setup
and hardware. I have a query that I run on the prod server and it takes just
3 seconds to complete while the same query takes about 1:06 Min on the DEV
server. When I do show execution plan it shows different.

The update usage or the database are identical and same in every respect.
I have tried rebuilding indexes at 90% fill factor yet no luck. I am not sur
e
whats wrong. I have tried everything I can think of. I have tried
Index defrag
REINDEX
ShowContig

Its been bugging me now.
The works fine on the dev server if I use a and clause. (6 sec)
but when I use an or clause it takes about 1.6 Min.

But irevelvant fo what I use ( and or or) on teh prod server it takes only 6
sec.

Please advice.

Is the edition of SQL Server the same on both?


--
Andrew J. Kelly  SQL MVP


"Jazzman" <Jazzman@discussions.microsoft.com> wrote in message
news:50F989CB-B3F5-4DF1-91DE- BF0E476C3772@microso
ft.com...
>I have two server Production and Development. Both are idenditical in setup
> and hardware. I have a query that I run on the prod server and it takes
> just
> 3 seconds to complete while the same query takes about 1:06 Min on the DEV
> server. When I do show execution plan it shows different.
>
> The update usage or the database are identical and same in every respect.
> I have tried rebuilding indexes at 90% fill factor yet no luck. I am not
> sure
> whats wrong. I have tried everything I can think of. I have tried
> Index defrag
> REINDEX
> ShowContig
>
> Its been bugging me now.
> The works fine on the dev server if I use a and clause. (6 sec)
> but when I use an or clause it takes about 1.6 Min.
>
> But irevelvant fo what I use ( and or or) on teh prod server it takes only
> 6
> sec.
>
> Please advice.

How big are the databases?

If it's small enough... you might want to move the ProdDB to the dev server
or the dev DB to the prod server to see if behaivior stays the same for the
DB. If yuo have a statistics difference (or something else) in the DB's it
would be helpful to see that the issue is at the DB level rather than the
server level.

Other ideas:

*  you might want to post hte query plans. Can't hurt.
* are you sure the service pack levels are the same between each server?



--

Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www. solidqualitylearning
.com


"Jazzman" <Jazzman@discussions.microsoft.com> wrote in message
news:50F989CB-B3F5-4DF1-91DE- BF0E476C3772@microso
ft.com...
> I have two server Production and Development. Both are idenditical in
 setup

> and hardware. I have a query that I run on the prod server and it takes
just
> 3 seconds to complete while the same query takes about 1:06 Min on the DEV
> server. When I do show execution plan it shows different.
>
> The update usage or the database are identical and same in every respect.
> I have tried rebuilding indexes at 90% fill factor yet no luck. I am not
sure
> whats wrong. I have tried everything I can think of. I have tried
> Index defrag
> REINDEX
> ShowContig
>
> Its been bugging me now.
> The works fine on the dev server if I use a and clause. (6 sec)
>  but when I use an or clause it takes about 1.6 Min.
>
> But irevelvant fo what I use ( and or or) on teh prod server it takes only
6
> sec.
>
> Please advice.

The service pack levels is the only difference.
The prod is on SP3 and dev is on SP4.

How do I find the stat diferences on the two servers.

"Brian Moran" wrote:

> How big are the databases?
>
> If it's small enough... you might want to move the ProdDB to the dev serve
r
> or the dev DB to the prod server to see if behaivior stays the same for th
e
> DB. If yuo have a statistics difference (or something else) in the DB's it
> would be helpful to see that the issue is at the DB level rather than the
> server level.
>
> Other ideas:
>
> *  you might want to post hte query plans. Can't hurt.
> * are you sure the service pack levels are the same between each server?
>
>
>
> --
>
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www. solidqualitylearning
.com
>
>
> "Jazzman" <Jazzman@discussions.microsoft.com> wrote in message
> news:50F989CB-B3F5-4DF1-91DE- BF0E476C3772@microso
ft.com... 
> setup 
> just 
> sure 
> 6 
>
>
>

yes, they are same version.

"Andrew J. Kelly" wrote:

> Is the edition of SQL Server the same on both?
>
>
> --
> Andrew J. Kelly  SQL MVP
>
>
> "Jazzman" <Jazzman@discussions.microsoft.com> wrote in message
> news:50F989CB-B3F5-4DF1-91DE- BF0E476C3772@microso
ft.com... 
>
>
>

Jazzman  (Jazzman@discussions
.microsoft.com)  writes:
> How do I find the stat diferences on the two servers.

DBCC SHOW_STATISTICS

Did you say that the involved tables have the same size on both servers?

The difference in service pack could matter, but it's far from certain.

What about posting the query?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Ok here is the wired thing. I copied the same database to another server TES
T
and ran the query it took 6 seconds to complete. Then I did a Data and
object transfer from TEST to DEV and now the DEV works fine as well.

How can this be. Certainly, its the same data and nothing has changed a bit.
and now it seems to be working.

Please suggest.


"Erland Sommarskog" wrote:

> Jazzman  (Jazzman@discussions
.microsoft.com) writes: 
>
> DBCC SHOW_STATISTICS
>
> Did you say that the involved tables have the same size on both servers?
>
> The difference in service pack could matter, but it's far from certain.
>
> What about posting the query?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>
>

Ok here is the wired thing. I copied the same database to another server TES
T
and ran the query it took 6 seconds to complete. Then I did a Data and
object transfer from TEST to DEV and now the DEV works fine as well.

How can this be. Certainly, its the same data and nothing has changed a bit.
and now it seems to be working.

Please suggest.


"Brian Moran" wrote:

> How big are the databases?
>
> If it's small enough... you might want to move the ProdDB to the dev serve
r
> or the dev DB to the prod server to see if behaivior stays the same for th
e
> DB. If yuo have a statistics difference (or something else) in the DB's it
> would be helpful to see that the issue is at the DB level rather than the
> server level.
>
> Other ideas:
>
> *  you might want to post hte query plans. Can't hurt.
> * are you sure the service pack levels are the same between each server?
>
>
>
> --
>
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www. solidqualitylearning
.com
>
>
> "Jazzman" <Jazzman@discussions.microsoft.com> wrote in message
> news:50F989CB-B3F5-4DF1-91DE- BF0E476C3772@microso
ft.com... 
> setup 
> just 
> sure 
> 6 
>
>
>

Most probably different statistics.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/


"Jazzman" <Jazzman@discussions.microsoft.com> wrote in message
news:329ED3CE-43F3-498A-A833- 69897AC6BCDE@microso
ft.com...
> Ok here is the wired thing. I copied the same database to another server T
EST
> and ran the query it took 6 seconds to complete. Then I did a Data and
> object transfer from TEST to DEV and now the DEV works fine as well.
>
> How can this be. Certainly, its the same data and nothing has changed a bi
t.
> and now it seems to be working.
>
> Please suggest.
>
>
> "Erland Sommarskog" wrote:
>