Parallelism and performance

Hi,
We have a stored procedure that runs slow when the execution plan uses
parallelism vs. if does not.
My Question is when is it helpful to have parallelism and when not. Also is
it advisable to configure the sql server to not use parallelism at all.
Thanks
RahulSometimes SQL Server just creates parallel plans which do not perform well,
although this seems to
be less and less of an issue with each release (and service pack). You can d
isable parallelism by:
MAXDOP 1 optimizer hint in the query
sp_configure and set "max degree of parallelism" to 1, will affect all queri
es
The "cost threshold for parallelism" affects the threshold for when parallel
plans are considered
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rahul" <reach_aggarwal@.hotmail.com> wrote in message news:e0QcAwyOHHA.1248@.TK2MSFTNGP02.phx
.gbl...
> Hi,
> We have a stored procedure that runs slow when the execution plan uses par
allelism vs. if does
> not.
> My Question is when is it helpful to have parallelism and when not. Also i
s it advisable to
> configure the sql server to not use parallelism at all.
> Thanks
> Rahul
>|||On Thu, 18 Jan 2007 19:18:26 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>Sometimes SQL Server just creates parallel plans which do not perform well,
although this seems to
>be less and less of an issue with each release (and service pack). You can
disable parallelism by:
>MAXDOP 1 optimizer hint in the query
>sp_configure and set "max degree of parallelism" to 1, will affect all quer
ies
>The "cost threshold for parallelism" affects the threshold for when parallel plans
are considered
Hey Tibor, I had 21 copies of my SPID yesterday on a select query, on
a box with four processors.
I think that set a new personal best for me!
J.|||Sounds like a query that needs optimization.
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:ovi0r2tt33p1do0ialubbtbn5ujlfua75a@.
4ax.com...
> On Thu, 18 Jan 2007 19:18:26 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Hey Tibor, I had 21 copies of my SPID yesterday on a select query, on
> a box with four processors.
> I think that set a new personal best for me!
> J.
>|||On Fri, 19 Jan 2007 09:50:24 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:

>Sounds like a query that needs optimization.
There must have been at least 21 rows to check!
J.