Hyperthreading

We just had this posted on www.sql-server-performance.com . I was wondering
if a Microsoft engineer or SQL Server expert would mind commenting on it.
The Question: Parallelism Must Be Set To Automatic to get full advantage of
SQL Server 2000.
SQL Server 2000 cannot use Parallel Query Processing, this is an unofficial
bug, not confirmed and by doing this the queries (transactions) proceeds
without any delay or errors will not occur.
So the recommendation is to run the Transact SQL script below or use the
Enterprise Manager.
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'max degree of parallelism', 1
GO
RECONFIGURE
GO
Please post if this solved your problem?
BooksOnline:
Parallel Query Processing
max degree of parallelism Option
Setting Configuration Options
How to configure the cost threshold for parallelism
Best Regards
- Member of The Professional Association for SQL Server, SQLPASS Denmark -
- Stability instead of incremental improvements -
Can you please post a link to the article / discussion so I can read the
original posting, this seems either not to contain enough information to
make any recommendation or it is taken out of context. It sounds really
weird to me, we are using hyperthreaded machines at lots of places without a
problem, as long as you are using Windows Server 2003 and not Windows 2000
or even worse NT 4.0
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"MeanOldDBA (derrickleggett@.hotmail.com)" <MeanOldDBA
(derrickleggett@.hotmail.com)@.discussions.microsoft .com> wrote in message
news:5F72525E-C851-4B8E-9404-F745FC07A690@.microsoft.com...
> We just had this posted on www.sql-server-performance.com . I was
> wondering
> if a Microsoft engineer or SQL Server expert would mind commenting on it.
> The Question: Parallelism Must Be Set To Automatic to get full advantage
> of
> SQL Server 2000.
> SQL Server 2000 cannot use Parallel Query Processing, this is an
> unofficial
> bug, not confirmed and by doing this the queries (transactions) proceeds
> without any delay or errors will not occur.
> So the recommendation is to run the Transact SQL script below or use the
> Enterprise Manager.
> sp_configure 'show advanced options', 1
> GO
> RECONFIGURE
> GO
> sp_configure 'max degree of parallelism', 1
> GO
> RECONFIGURE
> GO
>
> Please post if this solved your problem?
>
> BooksOnline:
> Parallel Query Processing
> max degree of parallelism Option
> Setting Configuration Options
> How to configure the cost threshold for parallelism
> Best Regards
> - Member of The Professional Association for SQL Server, SQLPASS Denmark -
> - Stability instead of incremental improvements -
|||Here's the link:
http://www.sql-server-performance.co...?TOPIC_ID=5306
"Gert E.R. Drapers" wrote:

> Can you please post a link to the article / discussion so I can read the
> original posting, this seems either not to contain enough information to
> make any recommendation or it is taken out of context. It sounds really
> weird to me, we are using hyperthreaded machines at lots of places without a
> problem, as long as you are using Windows Server 2003 and not Windows 2000
> or even worse NT 4.0
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2004 All rights reserved.
> "MeanOldDBA (derrickleggett@.hotmail.com)" <MeanOldDBA
> (derrickleggett@.hotmail.com)@.discussions.microsoft .com> wrote in message
> news:5F72525E-C851-4B8E-9404-F745FC07A690@.microsoft.com...
>
>
|||I have never heard of Janus Morthorst, or his claim that "SQL Server
2000 cannot use Parallel Query Processing".
I have never experienced that SQL-Server would not use parallelism if it
was benificial.
Check out
http://groups.google.nl/groups?hl=nl...1B49A98D%2540t
oomuchspamalready.nl%26rnum%3D1
(url may wrap) for a script that will use all available CPU's.
To comment on your original question "Parallelism Must Be Set To
Automatic to get full advantage of SQL Server 2000?" is that this is
obvious. If you limit parallelism to less than the available CPU's
(assuming it is a dedicated server), then SQL-Server will not be able to
fully use the system CPU capacity which can result in suboptimal query
execution.
Gert-Jan
MeanOldDBA wrote:[vbcol=seagreen]
> Here's the link:
> http://www.sql-server-performance.co...?TOPIC_ID=5306
> "Gert E.R. Drapers" wrote:
(Please reply only to the newsgroup)
|||Agree with Gert-Jan here, this seems like an complete unfunded response.
Anyhow on a HT machine my experience is that you are best of by setting the
MAXDOP to the number of physical processors, so on a 4-way with HT (8
logical procs) you set sp_configure 'max degree of parallelism', 4
It does not make much sense to have a single query use all 8 schedulers. HT
is a great feature and in general SQL Server benefits between 10-15% varying
the workload, but the 2 logical procs share L1 and L2 caches, so if you have
a load where the first logical proc trashes the L1-L2 caches it works
against the second logical proc, which is one of the reasons why you will
never see a second logical proc go to 100% CPU, but max around 50% instead
where the 1st logical proc can go to 100%.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:4148A942.1AF13239@.toomuchspamalready.nl...
>I have never heard of Janus Morthorst, or his claim that "SQL Server
> 2000 cannot use Parallel Query Processing".
> I have never experienced that SQL-Server would not use parallelism if it
> was benificial.
> Check out
> http://groups.google.nl/groups?hl=nl...0.1B49A98D%254
0toomuchspamalready.nl%26rnum%3D1
> (url may wrap) for a script that will use all available CPU's.
> To comment on your original question "Parallelism Must Be Set To
> Automatic to get full advantage of SQL Server 2000?" is that this is
> obvious. If you limit parallelism to less than the available CPU's
> (assuming it is a dedicated server), then SQL-Server will not be able to
> fully use the system CPU capacity which can result in suboptimal query
> execution.
> Gert-Jan
>
> MeanOldDBA wrote:
> --
> (Please reply only to the newsgroup)