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 disable parallelism by:
MAXDOP 1 optimizer hint in the query
sp_configure and set "max degree of parallelism" to 1, will affect all queries
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 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
> 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 queries
>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:
>>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
>>queries
>>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.
>|||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.sql

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
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 queries
>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.

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.

Painfully slow scripting

Using SqlExpress 2005, I have a database with over 1000 stored procedures
for a project still in development. From time-to-time I need to script out
the stored procedures for backup or transfer to another system. For the past
year, this has been a relatively quick scripting process, but lately it has
become excrutiatingly slow. There are no issues with the OS or the hardware
and all other software runs normally. Firewall is on, virus protection and
scan is up-to-date and no goofy pop-up issues nor any apparent malicious
software. Any thoughts on this issue would be appreciated.I think that Erland (I believe it was) had some improvements by setting forced parameterization (see
ALTER DATABASE).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Earl" <earl@.nospam.com> wrote in message news:uZLfdu5%23HHA.4200@.TK2MSFTNGP04.phx.gbl...
> Using SqlExpress 2005, I have a database with over 1000 stored procedures for a project still in
> development. From time-to-time I need to script out the stored procedures for backup or transfer
> to another system. For the past year, this has been a relatively quick scripting process, but
> lately it has become excrutiatingly slow. There are no issues with the OS or the hardware and all
> other software runs normally. Firewall is on, virus protection and scan is up-to-date and no goofy
> pop-up issues nor any apparent malicious software. Any thoughts on this issue would be
> appreciated.
>|||Earl (earl@.nospam.com) writes:
> Using SqlExpress 2005, I have a database with over 1000 stored
> procedures for a project still in development. From time-to-time I need
> to script out the stored procedures for backup or transfer to another
> system. For the past year, this has been a relatively quick scripting
> process, but lately it has become excrutiatingly slow. There are no
> issues with the OS or the hardware and all other software runs normally.
> Firewall is on, virus protection and scan is up-to-date and no goofy
> pop-up issues nor any apparent malicious software. Any thoughts on this
> issue would be appreciated.
Yes, as Tibor set, you can gain speed by saying
ALTER DATABASE db SET PARAMETERIZATION FORCED
this setting causes SQL Server to parameterize every query sent to it.
That is, a query like:
SELECT * FROM Orders WHERE OrderStatus = 'Bad' AND OrderDate < '20070101'
will be put into the cache as:
SELECT * FROM Orders WHERE OrderStatus = @.1 AND OrderDate < @.2
This measure can be a big speed winner with an application that generates
SQL with inlined parameter values, despite this being bad practice. And Mmgt
Studio is such an application. It's certainly embarrassing that Microsoft
themselves cannot adhere to good practice.
The reason this is a winner is that when the same query is repeated all
over again, just with different parameter values, SQL Server does not have
to spend time on compiling the queries.
But you should not have forced parameterization with a well-written
application that uses parameterised queries or stored procedures, since
SQL my parameterise where it shouldn't.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx