execution plan discrepancy

I have three SQL 7 servers - A, B, & C, each configured
identically. I have a query that essentially queries three
tables on server C.
When executing this query remotely from server A, I see a
different execution plan as opposed to running the same
query from server B.
From the execution plan, on server A, I see only one
remote query (execution time: 2 secs):
|--Stream Aggregate(GROUP BY:...
|--Remote Query(SELECT Col1181,...
However, on server B, I see several remote queries
(execution time: over 2 minutes):
| |--Remote Query(SELECT...
|--Sort(ORDER BY:([...
|--Hash Match(Inner Join, HASH:([...
|--Remote Query(SELECT
|--Hash Match(Inner Join, HASH:...
|--Hash Match(Inner Join, HASH:(...
| |--Remote Query(SELECT...
| |--Remote Query(SELECT...
|--Remote Query(SELECT...
Any ideas what could be causing this discrepancy and how I
could correct it. Thank you very much... appreciate all
your responses.Hi Rob.
Has Match usually occurs to support large join operations over columns with
no useful index.
Even though your servers are configured "identically", are the index
statistics up to date on each server? Perhaps one of the servers is
considering the index on the remote table useful enough to perform the whole
plan ermotely?
What about activity at the time the queries are run? Differing levels of
activity can yield different plans.
Are you logging on to each server in exactly the same manner? Different
security context, for example, can yield different plans.
These are just a few ideas.
Regards,
Greg Linwood
SQL Server MVP
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:0a4d01c3afb3$8622a4d0$a001280a@.phx.gbl...
> I have three SQL 7 servers - A, B, & C, each configured
> identically. I have a query that essentially queries three
> tables on server C.
> When executing this query remotely from server A, I see a
> different execution plan as opposed to running the same
> query from server B.
> From the execution plan, on server A, I see only one
> remote query (execution time: 2 secs):
> |--Stream Aggregate(GROUP BY:...
> |--Remote Query(SELECT Col1181,...
> However, on server B, I see several remote queries
> (execution time: over 2 minutes):
> | |--Remote Query(SELECT...
> |--Sort(ORDER BY:([...
> |--Hash Match(Inner Join, HASH:([...
> |--Remote Query(SELECT
> |--Hash Match(Inner Join, HASH:...
> |--Hash Match(Inner Join, HASH:(...
> | |--Remote Query(SELECT...
> | |--Remote Query(SELECT...
> |--Remote Query(SELECT...
> Any ideas what could be causing this discrepancy and how I
> could correct it. Thank you very much... appreciate all
> your responses.