Query Plan Differences

Hello all,
I have an interesting problem that I hope some of you can help me resolve.
We have a Production Server (PS) that uses Transactional Replication to send
data to a Reporting Server (RS). We have 3 horizontally partitioned tables
(based upon year) that are accessed via a view. On top of the year view we
have another view that grabs MAX values based upon an ID Code (not an
identity column). A query using the MAX view returns in 10 - 15 seconds on
the PS. The same query returns in 40 minutes on the RS. The keys, indexes
and server configuration are basically identical on both machines. However,
when I compare the execution plans, they are completely different. Both
machines are running W2K AS sp3 with SQL Ent Ed sp4, but there are some
differences:
PS: 6 - 700Mhz procs, 6GB RAM
RS: 6 - 550Mhx procs, 2.75GB RAM
Both machines are Proliant 8000's with a 5300 Array Controller that has 1
channel used for the OS (RAID1) and another channel used for the PageFile
(RAID1). They also have an HBA that connects them to RAID5 (data) arrays
and RAID0+1 (Tlogs) arrays on an HP EMA12000 SAN. While I would expect the
query to run faster on PS, I would not expect a different query plan. I
have tried re-indexing the RS, verified statistics and turning off
parallelism. No luck. Any suggestions would be greatly appreciated.
Thanks in advance.i would try updating statistics with full scan on each
system.
i am assuming statistics are not replicated, and that each
system will maintain statistics as necessary with the
default sampling.
while the sampling percentages are the same, there is no
guarantee that the sampled values are the same,
hence a fullscan is required to generate matching
statistics
>--Original Message--
>Hello all,
>I have an interesting problem that I hope some of you can
help me resolve.
>We have a Production Server (PS) that uses Transactional
Replication to send
>data to a Reporting Server (RS). We have 3 horizontally
partitioned tables
>(based upon year) that are accessed via a view. On top
of the year view we
>have another view that grabs MAX values based upon an ID
Code (not an
>identity column). A query using the MAX view returns in
10 - 15 seconds on
>the PS. The same query returns in 40 minutes on the RS.
The keys, indexes
>and server configuration are basically identical on both
machines. However,
>when I compare the execution plans, they are completely
different. Both
>machines are running W2K AS sp3 with SQL Ent Ed sp4, but
there are some
>differences:
>PS: 6 - 700Mhz procs, 6GB RAM
>RS: 6 - 550Mhx procs, 2.75GB RAM
>Both machines are Proliant 8000's with a 5300 Array
Controller that has 1
>channel used for the OS (RAID1) and another channel used
for the PageFile
>(RAID1). They also have an HBA that connects them to
RAID5 (data) arrays
>and RAID0+1 (Tlogs) arrays on an HP EMA12000 SAN. While
I would expect the
>query to run faster on PS, I would not expect a different
query plan. I
>have tried re-indexing the RS, verified statistics and
turning off
>parallelism. No luck. Any suggestions would be greatly
appreciated.
>Thanks in advance.
>
>.
>|||I have already tried that. Unfortunately, it did not change the results.
"joe chang" <anonymous@.discussions.microsoft.com> wrote in message
news:053901c39f67$b0c42bc0$a501280a@.phx.gbl...
> i would try updating statistics with full scan on each
> system.
> i am assuming statistics are not replicated, and that each
> system will maintain statistics as necessary with the
> default sampling.
> while the sampling percentages are the same, there is no
> guarantee that the sampled values are the same,
> hence a fullscan is required to generate matching
> statistics
> >--Original Message--
> >Hello all,
> >I have an interesting problem that I hope some of you can
> help me resolve.
> >We have a Production Server (PS) that uses Transactional
> Replication to send
> >data to a Reporting Server (RS). We have 3 horizontally
> partitioned tables
> >(based upon year) that are accessed via a view. On top
> of the year view we
> >have another view that grabs MAX values based upon an ID
> Code (not an
> >identity column). A query using the MAX view returns in
> 10 - 15 seconds on
> >the PS. The same query returns in 40 minutes on the RS.
> The keys, indexes
> >and server configuration are basically identical on both
> machines. However,
> >when I compare the execution plans, they are completely
> different. Both
> >machines are running W2K AS sp3 with SQL Ent Ed sp4, but
> there are some
> >differences:
> >
> >PS: 6 - 700Mhz procs, 6GB RAM
> >RS: 6 - 550Mhx procs, 2.75GB RAM
> >
> >Both machines are Proliant 8000's with a 5300 Array
> Controller that has 1
> >channel used for the OS (RAID1) and another channel used
> for the PageFile
> >(RAID1). They also have an HBA that connects them to
> RAID5 (data) arrays
> >and RAID0+1 (Tlogs) arrays on an HP EMA12000 SAN. While
> I would expect the
> >query to run faster on PS, I would not expect a different
> query plan. I
> >have tried re-indexing the RS, verified statistics and
> turning off
> >parallelism. No luck. Any suggestions would be greatly
> appreciated.
> >Thanks in advance.
> >
> >
> >.
> >