Performance gain by switching from Win2000 to Win2003?

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.
Andrew J. Kelly SQL MVP
"Peter Yao" <peteryao@.NoSPAMhotmail.com> wrote in message
news:O4Sovm9ZFHA.1940@.TK2MSFTNGP10.phx.gbl...
> 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.
>
|||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

"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OO3aO$CaFHA.4040@.TK2MSFTNGP14.phx.gbl...
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.
Andrew J. Kelly SQL MVP
"Peter Yao" <peteryao@.NoSPAMhotmail.com> wrote in message
news:O4Sovm9ZFHA.1940@.TK2MSFTNGP10.phx.gbl...
> 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.
>

Performance gain by switching from Win2000 to Win2003?

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.
--
Andrew J. Kelly SQL MVP
"Peter Yao" <peteryao@.NoSPAMhotmail.com> wrote in message
news:O4Sovm9ZFHA.1940@.TK2MSFTNGP10.phx.gbl...
> 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.
>|||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
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OO3aO$CaFHA.4040@.TK2MSFTNGP14.phx.gbl...
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.
--
Andrew J. Kelly SQL MVP
"Peter Yao" <peteryao@.NoSPAMhotmail.com> wrote in message
news:O4Sovm9ZFHA.1940@.TK2MSFTNGP10.phx.gbl...
> 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.
>

Performance gain by switching from Win2000 to Win2003?

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.
Andrew J. Kelly SQL MVP
"Peter Yao" <peteryao@.NoSPAMhotmail.com> wrote in message
news:O4Sovm9ZFHA.1940@.TK2MSFTNGP10.phx.gbl...
> 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.
>|||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
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OO3aO$CaFHA.4040@.TK2MSFTNGP14.phx.gbl...
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.
Andrew J. Kelly SQL MVP
"Peter Yao" <peteryao@.NoSPAMhotmail.com> wrote in message
news:O4Sovm9ZFHA.1940@.TK2MSFTNGP10.phx.gbl...
> 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.
>sql

Performance 1proc vs. 2proc

We are purchasing a new Win2003 Server with SQL Server Standard. Due to the
pricing structure of PER PROCESSOR pricing, can someone tell me is the
benefit of a 2nd processor worth the cost on a database server that is
primarily a backend to multiple web based apps?
ThanksMSUTech,
There is no way to say without knowing your load. It is not a matter of the
client (e.g. web app, VB app, etc.) but a matter of the kind of queries that
you are running. If your workload is not very heavy and the queries return
only small row sets, then you are probably fine.
However, a second processor can come in very handy with a greater load or a
mixture of long and short running queries. Why? Because it actually has
another physical processor (and that does matter) to run additional requests
when one processor is very busy. Most of the benefits you can get with a
multi-core processor (not hyperthreaded) since the cores are mostly
independent AND because Microsoft views a multi-core processor as a single
processor. (Very nice of them.)
But, bottom line is that you have to figure the cost/benefit based on your
workload. How much time do you spend waiting? How long do your average and
maximum length queries run? How much are you updating the database? What
kind of locking or blocking are you seeing?
And get a multi-core processor since it has no impact on your licensing.
RLF
"MSUTech" <MSUTech@.discussions.microsoft.com> wrote in message
news:53BC8FF1-5DA6-47BC-9669-4C4AC48CC5FB@.microsoft.com...
> We are purchasing a new Win2003 Server with SQL Server Standard. Due to
> the
> pricing structure of PER PROCESSOR pricing, can someone tell me is the
> benefit of a 2nd processor worth the cost on a database server that is
> primarily a backend to multiple web based apps?
> Thanks|||Russell,
If you have more than 1 processor then at the very least SQL and have its
own processor and Windows and other processes could run on the other
processor if you configure the server that way. Sometimes we forget the
processes that Windows wants/needs to do while SQL is running.
Chris
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uPiawylpIHA.3428@.TK2MSFTNGP02.phx.gbl...
> MSUTech,
> There is no way to say without knowing your load. It is not a matter of
> the client (e.g. web app, VB app, etc.) but a matter of the kind of
> queries that you are running. If your workload is not very heavy and the
> queries return only small row sets, then you are probably fine.
> However, a second processor can come in very handy with a greater load or
> a mixture of long and short running queries. Why? Because it actually
> has another physical processor (and that does matter) to run additional
> requests when one processor is very busy. Most of the benefits you can
> get with a multi-core processor (not hyperthreaded) since the cores are
> mostly independent AND because Microsoft views a multi-core processor as a
> single processor. (Very nice of them.)
> But, bottom line is that you have to figure the cost/benefit based on your
> workload. How much time do you spend waiting? How long do your average
> and maximum length queries run? How much are you updating the database?
> What kind of locking or blocking are you seeing?
> And get a multi-core processor since it has no impact on your licensing.
> RLF
> "MSUTech" <MSUTech@.discussions.microsoft.com> wrote in message
> news:53BC8FF1-5DA6-47BC-9669-4C4AC48CC5FB@.microsoft.com...
>> We are purchasing a new Win2003 Server with SQL Server Standard. Due to
>> the
>> pricing structure of PER PROCESSOR pricing, can someone tell me is the
>> benefit of a 2nd processor worth the cost on a database server that is
>> primarily a backend to multiple web based apps?
>> Thanks
>|||Chris,
True enough. For example, even staying within the SQL Server domain,
running DTS / SSIS is another process.
If you have a dual processor machine, however, you need to license SQL
Server for two processors. The licensing does not have a provision for
promising never to use the other processor, although it is possible to do
so.
But a multi-core (dual or quad) processor brings most of the benefits of two
processors while still being licensed as a single processor. So, the
multi-core processor benefits would extend to Windows and other processes
that might run on the machine.
RLF
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:OhOpU9upIHA.6096@.TK2MSFTNGP06.phx.gbl...
> Russell,
> If you have more than 1 processor then at the very least SQL and have its
> own processor and Windows and other processes could run on the other
> processor if you configure the server that way. Sometimes we forget the
> processes that Windows wants/needs to do while SQL is running.
> Chris
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uPiawylpIHA.3428@.TK2MSFTNGP02.phx.gbl...
>> MSUTech,
>> There is no way to say without knowing your load. It is not a matter of
>> the client (e.g. web app, VB app, etc.) but a matter of the kind of
>> queries that you are running. If your workload is not very heavy and the
>> queries return only small row sets, then you are probably fine.
>> However, a second processor can come in very handy with a greater load or
>> a mixture of long and short running queries. Why? Because it actually
>> has another physical processor (and that does matter) to run additional
>> requests when one processor is very busy. Most of the benefits you can
>> get with a multi-core processor (not hyperthreaded) since the cores are
>> mostly independent AND because Microsoft views a multi-core processor as
>> a single processor. (Very nice of them.)
>> But, bottom line is that you have to figure the cost/benefit based on
>> your workload. How much time do you spend waiting? How long do your
>> average and maximum length queries run? How much are you updating the
>> database? What kind of locking or blocking are you seeing?
>> And get a multi-core processor since it has no impact on your licensing.
>> RLF
>> "MSUTech" <MSUTech@.discussions.microsoft.com> wrote in message
>> news:53BC8FF1-5DA6-47BC-9669-4C4AC48CC5FB@.microsoft.com...
>> We are purchasing a new Win2003 Server with SQL Server Standard. Due to
>> the
>> pricing structure of PER PROCESSOR pricing, can someone tell me is the
>> benefit of a 2nd processor worth the cost on a database server that is
>> primarily a backend to multiple web based apps?
>> Thanks
>>
>|||I should have been a little clearer with multiple CPU's meaning multi-core
for licencing benefits.
Chris
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:e3Bs3PvpIHA.4928@.TK2MSFTNGP04.phx.gbl...
> Chris,
> True enough. For example, even staying within the SQL Server domain,
> running DTS / SSIS is another process.
> If you have a dual processor machine, however, you need to license SQL
> Server for two processors. The licensing does not have a provision for
> promising never to use the other processor, although it is possible to do
> so.
> But a multi-core (dual or quad) processor brings most of the benefits of
> two processors while still being licensed as a single processor. So, the
> multi-core processor benefits would extend to Windows and other processes
> that might run on the machine.
> RLF
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:OhOpU9upIHA.6096@.TK2MSFTNGP06.phx.gbl...
>> Russell,
>> If you have more than 1 processor then at the very least SQL and have its
>> own processor and Windows and other processes could run on the other
>> processor if you configure the server that way. Sometimes we forget the
>> processes that Windows wants/needs to do while SQL is running.
>> Chris
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:uPiawylpIHA.3428@.TK2MSFTNGP02.phx.gbl...
>> MSUTech,
>> There is no way to say without knowing your load. It is not a matter of
>> the client (e.g. web app, VB app, etc.) but a matter of the kind of
>> queries that you are running. If your workload is not very heavy and
>> the queries return only small row sets, then you are probably fine.
>> However, a second processor can come in very handy with a greater load
>> or a mixture of long and short running queries. Why? Because it
>> actually has another physical processor (and that does matter) to run
>> additional requests when one processor is very busy. Most of the
>> benefits you can get with a multi-core processor (not hyperthreaded)
>> since the cores are mostly independent AND because Microsoft views a
>> multi-core processor as a single processor. (Very nice of them.)
>> But, bottom line is that you have to figure the cost/benefit based on
>> your workload. How much time do you spend waiting? How long do your
>> average and maximum length queries run? How much are you updating the
>> database? What kind of locking or blocking are you seeing?
>> And get a multi-core processor since it has no impact on your licensing.
>> RLF
>> "MSUTech" <MSUTech@.discussions.microsoft.com> wrote in message
>> news:53BC8FF1-5DA6-47BC-9669-4C4AC48CC5FB@.microsoft.com...
>> We are purchasing a new Win2003 Server with SQL Server Standard. Due
>> to the
>> pricing structure of PER PROCESSOR pricing, can someone tell me is the
>> benefit of a 2nd processor worth the cost on a database server that is
>> primarily a backend to multiple web based apps?
>> Thanks
>>
>>
>