Currently have Win2000 Enterprise, 4 Xeon CPU, 4 GB memory.
Spec for Win2003 mentions better use of hyperthreading for SQL Server and IO
performance.
Do you leave hyperthreading on at BIOS level for OS to use but prevent SQL
Server from using it by unchecking the last few set of the processors in EM?

Can I expect a 5% to 10% perf. increase by upgrading from Win2000 to
Win2003?

Also thinking about upgrading 4GB to 8GB using /PAE is there a heavy perf.
hit by the /PAE addressing?
The cache stat on Prepared is at 60%.

Thanks.

It is impossible to say how much improvement you will get as it depends on
so many things. But in general if you are using any of the resources
moderate to heavily you should see some improvements.  Basically the Memory
utilization is much better, the I/O and Networking has greatly improved and
scheduling is much better as well.  Win2003 is HT aware and will better
support it over Win2K but you should start by setting the MAXDOP to the
number of physical processors and see how that goes.  PAE does not add that
much overhead and if you actually need more memory it is the way to go.  To
let SQL Server use more than 4GB you will also need to enable AWE.

Show quoteHide quote

Unfortunately, there is no Win2K EE, only AS.

Also, AWE will help with offloading lower 2 GB memory for data pages, but
not much else.  This could releave some pressure you are experiencing on
your PROC CACHE, which is limited to the 4 GB region regardless of AWE or
PAE.

Are you currently using the /3GB boot.ini switch.  I would try this first to
see if your PROC CACHE situation improves first before going through the
time and effort of an upgrade and/or adding additional memory.  AWE adds
functionality but it also adds new headaches.

If this doesn't help, I doubt going to AWE will either.  The best long term
solution, then, would be to start checking out the 64-bit platforms, IA64,
not that x64, EMT64 garbage.

Run DBCC PROCCACHE to see if you are being starved or not, both before and
after setting the /3GB switch.

I'm not so sure why there is so much negativity surrounding the use of HTT.
I've never had a problem with it even though I know there is a lot of
articles out there.  I wonder if the same sentiment exists over the new
multi-core chips?

Sincerely,


Anthony Thomas


It is impossible to say how much improvement you will get as it depends on
so many things. But in general if you are using any of the resources
moderate to heavily you should see some improvements.  Basically the Memory
utilization is much better, the I/O and Networking has greatly improved and
scheduling is much better as well.  Win2003 is HT aware and will better
support it over Win2K but you should start by setting the MAXDOP to the
number of physical processors and see how that goes.  PAE does not add that
much overhead and if you actually need more memory it is the way to go.  To
let SQL Server use more than 4GB you will also need to enable AWE.

Show quoteHide quote

Correct SELECT Statement via joins
Same Random order every time
Trying to determine the correct database replication to use.
Data File size
Error Addiing a PK Constraint
Server Performance Tunning
Waiting for type 0x4, current count 0x808, current owning EC 0x00000000.
Questions about SQL Server Logs under Management in EM
User Defined Functions allow parameters by reference???
SP4 for SQL2K....