4 prrocessors versus 1

We have four processors on our SQL Server and want to turn on all four now
that the legacy application that crashed earlier (before I began here) and is
no longer living in the system (we are running on only one processor). If
anyone has experience with gains related to turning on all four processors,
can we expect as much as a 15% performance gain, less?, more? none?
Regards,
Jamie
Well that depends on how they turned them off before. Did they use the
processor affinity mask or just MAXDOP?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:DAEF884D-1E5F-409C-86F6-979A69B406E0@.microsoft.com...
> We have four processors on our SQL Server and want to turn on all four now
> that the legacy application that crashed earlier (before I began here) and
> is
> no longer living in the system (we are running on only one processor). If
> anyone has experience with gains related to turning on all four
> processors,
> can we expect as much as a 15% performance gain, less?, more? none?
> --
> Regards,
> Jamie
|||You lost me. MAXDOP should be set to 2 on a two duals as opposed to just
turning it on? I do not know what was done before. I must say I am not sure
how MAXDOP is enabled.
Regards,
Jamie
"Andrew J. Kelly" wrote:

> Well that depends on how they turned them off before. Did they use the
> processor affinity mask or just MAXDOP?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:DAEF884D-1E5F-409C-86F6-979A69B406E0@.microsoft.com...
>
|||After reading through some documentation on processor affinity, it looks to
be used for Analysis Services which is something we run on the data warehouse
server rather than on the local production machine (through archiving). It
is the production machine where we are seeing problems with pagelatchio and
occasional timeouts (roughly a half million transactions per day in the
warehouse). If there is something that can be done to improve this
bottleneck issue, such as applying all four processors - the MAXDOP appears
to be something more akin to the Analysis services - or working both
processor affinitiy and MAXDOP into the equation (or not), it could be an
improvement. Analysis Services is not needed. The plan is to use only the
standard sql server service.
Don't need Analysis, just need more processing power. (SQL Std Ed 2000, ADV
SVR 2000, 6 gig RAM, 4 processor)
Quote from
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/anservog.mspx
The 32-bit version of Analysis Services can address up to 3 GB of memory in
the main process space, if you enable Application Memory Tuning. Unless you
enable Application Memory Tuning, no process can address more than 2 GB in
the main process space. To enable Application Memory Tuning on the Analysis
Services computer, set the /3 GB switch in the boot.ini file and then use
Analysis Manager to set an appropriate Memory conservation threshold value
for Analysis Services. If you set the /3GB switch in boot.ini, the computer
on which Analysis Services is running should have at least 4 GB of memory to
ensure that the Windows operating system has sufficient memory for system
services. If you are running other applications on the same computer, you
must factor in their memory requirements as well. For example, if the SQL
Server service and Analysis Services are installed on the same computer, SQL
Server can address memory above 4 GB because SQL Server supports Address
Windowing Extensions (AWE). In this case, you could install and use 8 GB or
more on the server. However, because Analysis Services does not support AWE,
Analysis Services cannot access more the 3 GB of memory in the main process
space unless the 64-bit version is used.
Regards,
Jamie
"Andrew J. Kelly" wrote:

> Well that depends on how they turned them off before. Did they use the
> processor affinity mask or just MAXDOP?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:DAEF884D-1E5F-409C-86F6-979A69B406E0@.microsoft.com...
>
|||On Tue, 14 Aug 2007 12:42:05 -0700, thejamie
<thejamie@.discussions.microsoft.com> wrote:

>We have four processors on our SQL Server and want to turn on all four now
>that the legacy application that crashed earlier (before I began here) and is
>no longer living in the system (we are running on only one processor). If
>anyone has experience with gains related to turning on all four processors,
>can we expect as much as a 15% performance gain, less?, more? none?
Assuming you turned them off at the bios level and now turn them back
on, ... hmm, will you have to do a reinstall of SQLServer? I wonder.
But that aside, if you were really running the entire machine on one
processor, and now run it on four, you will probably see a fairly
significant increase in performance, assuming a heavy but typical OLTP
load, say, 50% or more. SQLServer is always much happier on two
processors than on one, and somewhat happier on four than two. Plus
or minus dual-cores on them all. Assuming you have your IO and
storage and tempdb all configured well, too.
J.
|||On Tue, 14 Aug 2007 12:42:05 -0700, thejamie
<thejamie@.discussions.microsoft.com> wrote:

>We have four processors on our SQL Server and want to turn on all four now
>that the legacy application that crashed earlier (before I began here) and is
>no longer living in the system (we are running on only one processor).
HOW is it limited to run on only one processor? What setting was set?
Was processor affinity set to a specific processor?

>If
>anyone has experience with gains related to turning on all four processors,
>can we expect as much as a 15% performance gain, less?, more? none?
It depends. If the system has been CPU bound, meaning SQL Server has
been using close to 100% of one processor, then an improvement is
likely. If the system is running many small transactions, it should
be able to run more transactions at once before slowing down. If the
system is running large queries, some of those queries might benefit
from parallel processing and run quicker. (Some processes could end
up running slower in parallel, so watch out for that.)
On the other hand if the single CPU is only running at 50% activity,
and the bottleneck is the disk system, increasing the number of
processors is not going to help much at all.
Roy Harvey
Beacon Falls, CT
|||
> HOW is it limited to run on only one processor? What setting was set?
> Was processor affinity set to a specific processor?
>Roy,
At some point, the administrator who preceded me decided the issue was
related to a VB6 application (VB6-SP1). At that point, he limited the
processing to a single processor. He is here no longer but the current
netadmin is aware of the situation and has maintained the configuration in
that manner.
Regards,
Jamie
|||On Wed, 15 Aug 2007 08:38:05 -0700, thejamie
<thejamie@.discussions.microsoft.com> wrote:

>At some point, the administrator who preceded me decided the issue was
>related to a VB6 application (VB6-SP1). At that point, he limited the
>processing to a single processor. He is here no longer but the current
>netadmin is aware of the situation and has maintained the configuration in
>that manner.
At the risk of sounding like a broken record, HOW did he limit the
processing to a single processor?
Roy Harvey
Beacon Falls, CT
|||There is a setting on SQL Server. Use (dropdown select) processors. 1 is
selected. It is possible to select all four or any combination thereof.
Regards,
Jamie
"Roy Harvey" wrote:

> On Wed, 15 Aug 2007 08:38:05 -0700, thejamie
> <thejamie@.discussions.microsoft.com> wrote:
>
> At the risk of sounding like a broken record, HOW did he limit the
> processing to a single processor?
> Roy Harvey
> Beacon Falls, CT
>
|||On Wed, 15 Aug 2007 09:12:01 -0700, thejamie
<thejamie@.discussions.microsoft.com> wrote:

>There is a setting on SQL Server. Use (dropdown select) processors. 1 is
>selected. It is possible to select all four or any combination thereof.
So the SQL Server option for processor affinity was set.
In general it is a very good idea to give SQL Server full use of all
processors. The improvement could range from very slight on a system
that was not working very hard, to profound on a system that was
heavily CPU bound. The one thing to be aware of is that occasionally
there are queries that run slower with parallel plans. The simple
workaround for those special cases is to set MAXDOP for the specific
query. If that is not possible then SQL Server can be configured to
limit each query to a single processor by setting the "max degree of
parallelism" configuration option.
Roy Harvey
Beacon Falls, CT