Parallelism query

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.
KevHi Kev,
Requirement for multi processors/Degree of parallelism depends upon the kind
of transaction thats been executed on the server.
If your nature of query includes more of insert, delete and update statments
(doesn't include heavy complicated queries), then query optimizer is wasting
its time evaluating each query to see if it can take advantage of
parallelism.
On the other hand, if the nature of queries include complex joins,
correlated subqueries, which takes maximum amount of time to execute the
query, then it makes sense to have DOP > 1.
You can read more about this at
http://www.sql-server-performance.c...on_settings.asp
- - - - - - - - -
Thanks
Yogish
"Network SI (UK) Ltd" wrote:

> 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
>|||Your third party developer doesn't know what he is talking about. There is
overhead when you use query parallelism (of course) but that doesn't mean
you shouldn't use it. 2 processors working at 90% of their capacity still
return the results quicker than 1 processor working at 100%. Depending on
how your server is used, it is usually be a good idea to limit the maximum
number of processors that can be used per query, so that one query doesn't
block all the processors. What the ideal limit is, is something you have to
experiment with, depending on the number of large select queries vs.
insert/update/inserts and the performance you want of them respectively. For
example, if performance of updates is of the highest importance, and
performance of reporting on the server is of less importance, you would set
the maximum number of processors for parallelism to 1. On the other side of
the scale is something like a staging/processing server for a datawarehouse,
where there's mostly only one process running at a time. In that case you
set the maximum number of processors for parallelism to the number of
physical processors you have.
I don't completely agree with the technet recommendation. Multiple
connections can share one NIC, and processor usage and network traffic are
usually somewhat correlated, but it is by no means straightforward.
SELECT * FROM multi_million_row_table
will cause a lot of network traffic, but not a lot of processor activity,
where
SELECT * FROM multi_million_row_table WHERE unindexed_column = <some value>
will cause less network traffic, but more processor activity.
Jacco Schalkwijk
SQL Server MVP
"Network SI (UK) Ltd" <Kev Smart@.discussions.microsoft.com> wrote in message
news:575D585F-2F48-4761-9479-1328DFE31E73@.microsoft.com...
> 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
>|||Following the logic of the 3rd party developer why not just scrap the big
iron and by a cheap server box?
Try a few configurations and use whichever works best for you. I suspect
that you should be able to enable 6,7, or even all 8 processors. I assume
that you are talking about 8 physical processors (not 4 physical with
hyperthreading enabled).
Keith
"Network SI (UK) Ltd" <Kev Smart@.discussions.microsoft.com> wrote in message
news:575D585F-2F48-4761-9479-1328DFE31E73@.microsoft.com...
> 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
>|||Thank you for the 3 quick replies, confirming my feelings. Does anyone have
any 'Best Practise' recommendations or is it best to suck it and see?
Kev
Network SI (UK) Ltd
"Keith Kratochvil" wrote:

> Following the logic of the 3rd party developer why not just scrap the big
> iron and by a cheap server box?
> Try a few configurations and use whichever works best for you. I suspect
> that you should be able to enable 6,7, or even all 8 processors. I assume
> that you are talking about 8 physical processors (not 4 physical with
> hyperthreading enabled).
> --
> Keith
>
> "Network SI (UK) Ltd" <Kev Smart@.discussions.microsoft.com> wrote in messa
ge
> news:575D585F-2F48-4761-9479-1328DFE31E73@.microsoft.com...
>
>|||Best thing would be to experiment and get some baseline. Get the a few of
the largest queries you have and run them with MAXDOP(1) to MAXDOP(8) (so
only those query are limited in the number of processors), and see what the
execution times are. It's pretty easy to create a script and run the whole
thing at a quiet time over the weekend or so
Jacco Schalkwijk
SQL Server MVP
"Network SI (UK) Ltd" <NetworkSIUKLtd@.discussions.microsoft.com> wrote in
message news:36784A55-E20F-4E2B-AE6B-BF38C4671F7E@.microsoft.com...[vbcol=seagreen]
> Thank you for the 3 quick replies, confirming my feelings. Does anyone
> have
> any 'Best Practise' recommendations or is it best to suck it and see?
> Kev
>
> --
> Network SI (UK) Ltd
>
> "Keith Kratochvil" wrote:
>sql