Dual Core CPUs and Parallelism

Hi,
In some articles, it is mentioned that Hyperthreaded processors might have
negative impact on query performance and the SQL Server must be configured
to use actual number of CPUs for parallelism. Even once I encountered this
situation and problem eliminated by using MAXDOP option for the query.
Is it true for Dual Core processors as well?
Thanks in advance,
Leila
On Wed, 14 Nov 2007 00:40:14 +0330, "Leila" <Leilas@.hotpop.com> wrote:

>In some articles, it is mentioned that Hyperthreaded processors might have
>negative impact on query performance and the SQL Server must be configured
>to use actual number of CPUs for parallelism. Even once I encountered this
>situation and problem eliminated by using MAXDOP option for the query.
>Is it true for Dual Core processors as well?
No, it is not true for multi-core processors. Multi-core processors
have an actual CPU for each core. Hyperthreading occurred on a single
CPU and didn't really do much.
Roy Harvey
Beacon Falls, CT
|||Leila,
This article only mentions hyperthreading:
http://support.microsoft.com/default.aspx/kb/322385 I understand that
dual-core is not the same as hyperthreading. This article applies the
principle to multi-core. Be sure to read the comments that follow for a
fuller picture:
http://blogs.msdn.com/sqltips/archive/2005/09/14/466387.aspx
So, they appear to be saying multi-core is not the same as hyperthreading,
but a little modesty in the parallelism is a good idea. (In other posts (if
you google around) you can find people who feel strongly that OLTP should
never use parallelism.)
This article http://www.sqlmag.com/Articles/ArticleID/97044/97044.html?Ad=1
by Andrew Kelly says, in part, "Typically, online transaction processing
(OLTP) systems benefit more from a lower degree of parallelism, and
reporting systems benefit more from higher ..."
FWIW,
RLF
"Leila" <Leilas@.hotpop.com> wrote in message
news:etHHemjJIHA.4272@.TK2MSFTNGP06.phx.gbl...
> Hi,
> In some articles, it is mentioned that Hyperthreaded processors might have
> negative impact on query performance and the SQL Server must be configured
> to use actual number of CPUs for parallelism. Even once I encountered this
> situation and problem eliminated by using MAXDOP option for the query.
> Is it true for Dual Core processors as well?
> Thanks in advance,
> Leila
>
|||Hyperthreading is not the same multi-core technology. Hyperthreading fakes
multi-processors, is a dated technology, and wasn't (still isn't) good for an
inherently multi-threaded app such as SQL Server.
But when it comes to query parallelism, hyperthreading and multi-core will
pretty much give you the same since SQL Server operates on logical
processors. So if there is a need for you to restrict MAXDOP under
hyperthreading, you need to restrict MAXDOP with multi-cores.
Linchi
"Leila" wrote:

> Hi,
> In some articles, it is mentioned that Hyperthreaded processors might have
> negative impact on query performance and the SQL Server must be configured
> to use actual number of CPUs for parallelism. Even once I encountered this
> situation and problem eliminated by using MAXDOP option for the query.
> Is it true for Dual Core processors as well?
> Thanks in advance,
> Leila
>
>
|||To add to Russell's comment about OLTP systems take a look at section 2 of
this link saying "Given the high volumes of OLTP, parallel queries usually
reduce OLTP throughput and should be avoided"
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/oltp-performance-issues.mspx
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:

> Leila,
> This article only mentions hyperthreading:
> http://support.microsoft.com/default.aspx/kb/322385 I understand that
> dual-core is not the same as hyperthreading. This article applies the
> principle to multi-core. Be sure to read the comments that follow for a
> fuller picture:
> http://blogs.msdn.com/sqltips/archive/2005/09/14/466387.aspx
> So, they appear to be saying multi-core is not the same as hyperthreading,
> but a little modesty in the parallelism is a good idea. (In other posts (if
> you google around) you can find people who feel strongly that OLTP should
> never use parallelism.)
> This article http://www.sqlmag.com/Articles/ArticleID/97044/97044.html?Ad=1
> by Andrew Kelly says, in part, "Typically, online transaction processing
> (OLTP) systems benefit more from a lower degree of parallelism, and
> reporting systems benefit more from higher ..."
> FWIW,
> RLF
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:etHHemjJIHA.4272@.TK2MSFTNGP06.phx.gbl...
>
>
|||Roy Harvey (SQL Server MVP) wrote:

> No, it is not true for multi-core processors. Multi-core processors
> have an actual CPU for each core. Hyperthreading occurred on a single
> CPU and didn't really do much.
>
But in multi-core processors cores share caches and I/O -ports. In
arithmetic tasks this does not matter, but what about DBMS servers, that
use both memory and I/O quite a lot? I guess dual processor is better
than dual core, for example.
Arto Viitanen
|||Paul Randall has a very good post about 2 dual-core chips vs. 4 single-core
chips. You can find his blog at www.sqlskills.com. The ultimate answer is
"It Depends", but he delves into several scanrios during his post.
Rick Heiges
SQL Server MVP
"Arto V Viitanen" <arto.viitanen@.pp4.inet.fi> wrote in message
news:6yH_i.242$Ru3.9@.read3.inet.fi...
> Roy Harvey (SQL Server MVP) wrote:
>
> But in multi-core processors cores share caches and I/O -ports. In
> arithmetic tasks this does not matter, but what about DBMS servers, that
> use both memory and I/O quite a lot? I guess dual processor is better
> than dual core, for example.
> --
> Arto Viitanen
|||On Wed, 14 Nov 2007 18:57:06 GMT, Arto V Viitanen
<arto.viitanen@.pp4.inet.fi> wrote:

>But in multi-core processors cores share caches and I/O -ports. In
>arithmetic tasks this does not matter, but what about DBMS servers, that
>use both memory and I/O quite a lot? I guess dual processor is better
>than dual core, for example.
No question that multi-core processors are a compromise, but the
original question compared a dual core to a single core with
hyperthreading - and they really are not comparable.
Roy Harvey
Beacon Falls, CT