salam

Also I forgot to ask, how can I distinguish between logical and physical. AS
I said I have CPU 0, CPU 1 CPU 2 CPU3

can assign for instance CPU0 and CPU 1 for SQL

Salam

brian moran

I don't think there is a way to show this through a tool, but... in this
case CPU 0 and CPU 2 will be one physical processor while, CPU1 and CPU3
will be the other....

--

Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com

salamonaltern

Thanks Brian, so in case I need to dedicate one physical processor to SQL,
If I understand well, I need to tell SQL use CPU 0 et CPU 2

Thanks again
Salam

alex

isn't SQL working with hyper-threading? will we pay for the hyper-threading
CPUs number if we buy CPU licenses or only for the physical ones?


Alex

hal berenson

You just pay for the physical cpus. Beyond that SQL Server 2000 does work
with HT, however it doesn't do anything really special for HT. So in some
instances you may get less than ideal performance.

The issue for SQL Server 2000 on HT, if there is one, is that SQL Server's
internal schedulers are tied to processors (which in this case should be
thought of as logical processors). Once work is assigned to a scheduler it
stays with that scheduler and thus that processor. So it would be possible
for two long running pieces of work to be assigned to the two logical
processors on the same physical processor and compete for that single
physical processor, even though another physical processor is less busy. A
similar situation can even happen without HT, but in that case at least the
two processors are equal in capability (that is you are getting 2X the
performance of a single processor). In the HT case you are getting more
less than 1.5X the performance of a single processor, so the impact is
exaggerated. For short-running transactions, as in an OLTP system, this
shouldn't be an issue. For systems with long running complex queries it
might be.

I actually wouldn't try to restrict SQL Server to the logical processors on
a subset of physical processors. What I would do is either (a) don't
restrict it at all (for most cases) or (b) restrict it to the first logical
processor of each physical processor. In the latter case this causes SQL
Server to only have one scheduler per physical processor and avoid
pathologically bad cases. And it leaves the second logical processor on
each physical processor free to run background tasks and other work assigned
to the server. On a 2 (physical) processor system with HT this means you
would restrict SQL Server to use CPUs 0 and 1.

--
Hal Berenson, SQL Server MVP
True Mountain Group LLC

alex

hi

sorry - stupid question - but what is a OLTP System? And what do you meen
with "long" running and "short-running"? are we talking about milliseconds
or minutes/hours? is there any simple way to find out the best config if i
don't know how the app perform? we are running a webserver in front of this
box... from my view mostly short querys :-). but there are some jobs running
up to 30min or 2h (once a day).


Alex

alex

are you sure? i will think CPU 0 and 2 or isn't it?

aside: it it possible to run a 4 CPU Xeon MP machine with Standard Edition
and HT (8 CPU = 4 physcial and 4 virtual)?


Alex

alex

i think so, too. but i haven't ever configured this :-)


Alex