Re: Query Cost in execution plan?

From: Gert-Jan Strik (sorry_at_toomuchspamalready.nl)
Date: 07/14/04 

Date: Wed, 14 Jul 2004 22:44:12 +0200

Bob,

If your goal is to maximize server throughput, and not to maximize
individual query speeds, then it is best to disable parallel processing.
An individual query will benefit from parallel processing (at least,
when SQL-Server chooses to execute in parallel), but the parallel query
plan will always use more resources than a serial query plan.

Theoretically, if your goal is to maximize server trhoughput, then you
would have to carefully balance I/O intensive queries versus CPU &
memory intensive queries. Parallel processing typically involves hashing
and is therefore quite CPU and memory intensive. Bookmark lookups are
generally rather I/O intensive.

Currently, there is no way to configure SQL-Server to optimize for
throughput.

HTH,
Gert-Jan

Bob Castleman wrote:
>
> OOPS! I meant this to be posted in the thread "Splitting workload to
> different processors" but hit the wrong button!
>
> What that thread wantes was avoiding the scenario of a large number
> crunching operation consuming the all the processors. Since their is no way
> to force a query or queries onto a processor, then if the operation involved
> multiple asynchronous queries or batches then it could still consume all the
> processors since individual queries could be assigned to separate
> processers, regardless of MAXDOP. What I'm thinking could happen is that if
> 4 queries are run asynchronously, then they could each be assigned to a
> different processor, regardless of the execution plans. The entire process
> would have to be in serial steps so that any piece of it started and
> completed before the next piece was invoked. Then setting the MAXDOP to 2
> would make sure that the individual queries never consumed more than 2
> processors. This serialization of the entire operation might have to be
> managed on the client side.
>
> Bob
>
> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
> news:eYmGrzcaEHA.556@tk2msftngp13.phx.gbl...
> > Bob,
> >
> > If the plan is involved enough that a parallel plan is generated there is
> > always a single plan generated as well. It is at runtime when the engine
> > decides to run on x many processors. There are several things that affect
> > this behavior such as the amount of free memory, the number of procs and
> how
> > busy they are, the number of users etc. So if you go to run a large
> > parallel operation and the server is busy it may not run in parallel at
> all
> > or may use half the avail procs etc. But if you want to ensure that no
> > query will ever use more than 2 out of the 4 procs you would have to set
> > MAXDOP.
> >
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Bob Castleman" <nomail@here> wrote in message
> > news:uW6tZlcaEHA.1840@TK2MSFTNGP11.phx.gbl...
> > > What effect does the optimizer play in this? If your running a lot of
> > small
> > > queries and they are under the cost threshold, the optimizer won't
> > generate
> > > parallel plans, right? Also, even if the cost threshold is breached, if
> > the
> > > number crunching involves asnychronous processing and mutiple queries,
> > > wouldn't this still allow the use of all available processors? It seems
> to
> > > me that you would have to have a serial process so that individual
> queries
> > > aren't assigned to separate processors then setting MAXDOP to 2 would
> > > prevent any individual query from utilizing more than 2 processors.
> > >
> > > Or am I way off base?
> > >
> > > Bob
> > > SuccessWare Software
> > >
> > >
> >
> > 

-- 
(Please reply only to the newsgroup)