Parallel vs. serial plan

Hi,
What would be the advantages of lengthy report parallel vs.
serial execution, say on a 4-way SMP system, if the query
in itself is not able to produce sustained load of more
than 4 seconds on a single CPU of the system. I'm using MS
SQL Server 2000 on MS Windows 2000 with the lastest service
packs.
--
Many thanks,
OskOsk wrote:
> Hi,
> What would be the advantages of lengthy report parallel vs.
> serial execution, say on a 4-way SMP system, if the query
> in itself is not able to produce sustained load of more
> than 4 seconds on a single CPU of the system. I'm using MS
> SQL Server 2000 on MS Windows 2000 with the lastest service
> packs.
I would say that 4 seconds is a long time in the CPU world. It may not
be an issue if this is run off-hours, but during production hours, if
run many times, it could be more of an issue. Parallel executions
generally consume more CPU than their single-CPU brothers. It's just
more costly to manage parallel executions. So in your case, you are
potentially tying up one CPU out of four for four seconds, leaving the
other three to perform other tasks. With a parallel plan, depending on
how many CPUs SQL Server chooses to use, you'd end up tying up more CPU
for more than four CPU seconds, but the query could finish in 2.5
seconds.
Are you saying you have disabled parallel plans for this query using a
MAXDOP hint or have changed the corresponding server setting to prevent
parallel executions?
--
David Gugick
Imceda Software
www.imceda.com|||SQL-Server does not know how important a particular query is for you. It
doesn't discriminate queries that way. It just estimates (for each
query) whether executing the parallel plan is likely to be faster than
the serial plan. If it is, and the server is not too stressed, then it
will execute the parallel plan. Otherwise, it will execute the serial
plan.
However, there is a threshold below which a parallel plan will not be
considered. By default, this "cost threshold for parallelism" is set to
5. With this setting, SQL-Server will not consider a parallel plan if
the estimated execution time of the serial plan is less than 5 seconds.
Hope this helps,
Gert-Jan
Osk wrote:
> Hi,
> What would be the advantages of lengthy report parallel vs.
> serial execution, say on a 4-way SMP system, if the query
> in itself is not able to produce sustained load of more
> than 4 seconds on a single CPU of the system. I'm using MS
> SQL Server 2000 on MS Windows 2000 with the lastest service
> packs.
> --
> Many thanks,
> Osk|||Yes, I have changed the 'max degree of parallelism' server
setting to 1. So as I understand it now, when the optimizer
thinks the query can benefit from executing in parllel at
the same time when the rest of CPUs are more or less idle
then, despite the parallel plan being more costly in terms
of CPU time, the query will return results faster. So I
need look at what kind of load is placed on the rest of
CPUs during the time the query runs to determine whether
there will be any benefit or parallelism.
>--Original Message--
>Osk wrote:
>> Hi,
>> What would be the advantages of lengthy report parallel vs.
>> serial execution, say on a 4-way SMP system, if the query
>> in itself is not able to produce sustained load of more
>> than 4 seconds on a single CPU of the system. I'm using MS
>> SQL Server 2000 on MS Windows 2000 with the lastest service
>> packs.
>I would say that 4 seconds is a long time in the CPU
world. It may not
>be an issue if this is run off-hours, but during
production hours, if
>run many times, it could be more of an issue. Parallel
executions
>generally consume more CPU than their single-CPU brothers.
It's just
>more costly to manage parallel executions. So in your
case, you are
>potentially tying up one CPU out of four for four seconds,
leaving the
>other three to perform other tasks. With a parallel plan,
depending on
>how many CPUs SQL Server chooses to use, you'd end up
tying up more CPU
>for more than four CPU seconds, but the query could finish
in 2.5
>seconds.
>Are you saying you have disabled parallel plans for this
query using a
>MAXDOP hint or have changed the corresponding server
setting to prevent
>parallel executions?
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||anonymous@.discussions.microsoft.com wrote:
> Yes, I have changed the 'max degree of parallelism' server
> setting to 1. So as I understand it now, when the optimizer
> thinks the query can benefit from executing in parllel at
> the same time when the rest of CPUs are more or less idle
> then, despite the parallel plan being more costly in terms
> of CPU time, the query will return results faster. So I
> need look at what kind of load is placed on the rest of
> CPUs during the time the query runs to determine whether
> there will be any benefit or parallelism.
>
I thik you might be overthinking this a bit. Why have you decided to
change the MAX DEGREE OF PARALLELISM on the server and not let SQL
Server manage parallel queries on its own? Occasionally, someone reports
an issue with a parallel plan which can be easily fixed with a MAXDOP
(1) in the query.
--
David Gugick
Imceda Software
www.imceda.com|||Well, that's how I understood it. If I'm wrong please
correct me.
I've set the server-wide MAXDOP to 1 because of two reasons:
1) to see whether placing the load of reports on a single
CPU will allow smaller queries have fulfilled their CPU
needs quicker than when they would have to wait for a
report to execute in parallel on all CPUs (although this
seems to work only in cases when the report doesn't hold
locks incompatible with other query's locks)
2) I can't provide hints at the T-SQL level yet
--
Thanks,
Osk
>--Original Message--
>anonymous@.discussions.microsoft.com wrote:
>> Yes, I have changed the 'max degree of parallelism' server
>> setting to 1. So as I understand it now, when the optimizer
>> thinks the query can benefit from executing in parllel at
>> the same time when the rest of CPUs are more or less idle
>> then, despite the parallel plan being more costly in terms
>> of CPU time, the query will return results faster. So I
>> need look at what kind of load is placed on the rest of
>> CPUs during the time the query runs to determine whether
>> there will be any benefit or parallelism.
>I thik you might be overthinking this a bit. Why have you
decided to
>change the MAX DEGREE OF PARALLELISM on the server and not
let SQL
>Server manage parallel queries on its own? Occasionally,
someone reports
>an issue with a parallel plan which can be easily fixed
with a MAXDOP
>(1) in the query.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||Osk wrote:
> Well, that's how I understood it. If I'm wrong please
> correct me.
> I've set the server-wide MAXDOP to 1 because of two reasons:
> 1) to see whether placing the load of reports on a single
> CPU will allow smaller queries have fulfilled their CPU
> needs quicker than when they would have to wait for a
> report to execute in parallel on all CPUs (although this
> seems to work only in cases when the report doesn't hold
> locks incompatible with other query's locks)
> 2) I can't provide hints at the T-SQL level yet
>
MAXDOP at the server level does not mean that reports are only going to
run on a single CPU. Unless what you're saying is that only one report
can run at a time and because of that it will only run on a single CPU.
MAXDOP 1 just means SQL Server will only use a single CPU for a query,
but the CPU used could be any available (maybe you already understood
that). Obviously, if 4 reports were executed simultaneously and you had
4 CPUs on the server, they could be running on different CPUs.
If you have concerns, I guess you could leave the option on, but I would
suggest you test with the default settings as well.
David Gugick
Imceda Software
www.imceda.com