| Author |
Same Query but different Execution plan.
|
|
| Jazzman 2005-08-30, 1:23 pm |
| 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.
| |
| Andrew J. Kelly 2005-08-30, 1:23 pm |
| 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.
| |
| Brian Moran 2005-08-30, 8:23 pm |
| 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.
| |
| Jazzman 2005-08-30, 8:23 pm |
| 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 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...
> setup
> just
> sure
> 6
>
>
>
| |
| Jazzman 2005-08-30, 8:23 pm |
| 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...
>
>
>
| |
| Erland Sommarskog 2005-08-30, 8:23 pm |
| 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
| |
| Jazzman 2005-08-31, 11:23 am |
| Ok here is the wired thing. I copied the same database to another server TEST
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
>
>
| |
| Jazzman 2005-08-31, 11:23 am |
| Ok here is the wired thing. I copied the same database to another server TEST
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 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...
> setup
> just
> sure
> 6
>
>
>
| |
| Tibor Karaszi 2005-08-31, 8:23 pm |
| 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...[color=darkred]
> Ok here is the wired thing. I copied the same database to another server TEST
> 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:
>
|
|
|
|