a query in parallel make such a dramatic difference to performance ?
We have a reasonably simple query which brings in data from a few none
complex views. If we run it on our 2x2.4Ghz Xeon server it takes 6
minutes plus to run. If we run this on the same server with
OPTION(MAXDOP 1) at the end of the same query it takes less than a
second.
Examining the execution plan, the only difference I have been able to
see is that parallelism is taking up 96% of the run time when using
two processors. This drops when using the one so a sort takes up the
vast majority of the time for the query to run.
OK, so running in parallel should mean that it's run in various parts
and then 'joined up' later for performance gains, but how can it get
it so wrong (timewise) ?
If this is the case, will I see a significant difference changing our
server to use a single processor, which seems completely the wrong
approach (or should I do this on each query in each app - eek) ?
Do we have a problem that we don't know about that causes it to take
this long ?
What can we do ? Ideally, using both processors would seem to be
preferrable.We've changed the server to use a single processor at the moment and
the report that my query is based on works almost instantly. We're
waiting to see what effect this has for other users, but so far,
no-one has complained.
Are we wasting a second processor ?|||"Ryan" <ryanofford@.hotmail.com> wrote in message
news:7802b79d.0312160816.68d60164@.posting.google.c om...
> We've changed the server to use a single processor at the moment and
> the report that my query is based on works almost instantly. We're
> waiting to see what effect this has for other users, but so far,
> no-one has complained.
> Are we wasting a second processor ?
No. There are definitely times it can help.
You may want to open a ticket with MS. In general, when the query optimizer
finds such a poor optimization they consider it a bug. (If you can, review
Kalen Delaney's article in this month's SQL Server Magazine.)
No comments:
Post a Comment