SQL 2000 Parallelism - is it worth it?

Hi,

I have a sql 2000 server with 8 processors, server settings are as
default. I read on Technet that it is good practise to remove the
highest no. processors from being used for parallelism, corresponding
to the no. of NICs in the server. One of our 3rd party developers has
recommended only allowing one processor to be used as there is a
performance hit by the server working out which processor to use. Does
anyone have a definitive answer to this? I suspect he's wrong but I'd
like some hard evidence if possible, thanks.

Kev.

I have no hard evidence as to this, but if you are working with
hyperthreaded processors (e.g. Xeons), then parallelism may impact you
since SQL Server will attempt to use a single physical processer as two
virtual processors sharing the same L2 cache. Kevin Kline mentioned
this in his presentation at DevTeach this year.

The whole idea behind parallelism is to finish sooner by using several
CPU's, so the story that it would in fact slow down individual queries
doesn't make any sense. And the parallelism threshold should prevent
this for queries that have a "obvious" query plan.

Also, without parallelism, SQL-Server also has to decide which CPU
should execute the query plan...

Gert-Jan


kev@earlshilton.com wrote:[color=blue]
>
> Hi,
>
> I have a sql 2000 server with 8 processors, server settings are as
> default. I read on Technet that it is good practise to remove the
> highest no. processors from being used for parallelism, corresponding
> to the no. of NICs in the server. One of our 3rd party developers has
> recommended only allowing one processor to be used as there is a
> performance hit by the server working out which processor to use. Does
> anyone have a definitive answer to this? I suspect he's wrong but I'd
> like some hard evidence if possible, thanks.
>
> Kev.[/color]

(kev@earlshilton.com) writes:[color=blue]
> I have a sql 2000 server with 8 processors, server settings are as
> default. I read on Technet that it is good practise to remove the
> highest no. processors from being used for parallelism, corresponding
> to the no. of NICs in the server. One of our 3rd party developers has
> recommended only allowing one processor to be used as there is a
> performance hit by the server working out which processor to use. Does
> anyone have a definitive answer to this? I suspect he's wrong but I'd
> like some hard evidence if possible, thanks.[/color]

In fact, it is not uncommon to see SQL Server pick a parallel plan
which is considerably slower than a non-parallel plan. However, I don't
think turning of parallelism entirely is really a good thing. There
are probably cases where you parallelism can help you to speed up
queries as well.

However, if you processors are hyperthreaded, you set "Max degree of
parallelism" to be at most 8, that is the number of physical processors.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp