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,
JamieWell 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...
> > 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
>|||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...
> > 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
>|||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:
>> 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.
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:
> >> 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.
> 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|||Actually I think they set the MAXDOP and not the affinity but from the lack
of solid information it is hard to say for sure. We don't even know what
version he is running. But I think in SQL2000 there was a drop down for the
# of procs to use for parallelism (MAXDOP) and affinity was checkboxes.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:um96c3td030rrn9hgi6r17n6b3qi8dmc0f@.4ax.com...
> 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|||On Wed, 15 Aug 2007 13:38:02 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>Actually I think they set the MAXDOP and not the affinity but from the lack
>of solid information it is hard to say for sure. We don't even know what
>version he is running. But I think in SQL2000 there was a drop down for the
># of procs to use for parallelism (MAXDOP) and affinity was checkboxes.
Well changing MAXDOP is unlikely to help keep OLTP go any quicker. It
might help some complex long running query.
Roy Harvey
Beacon Falls, CT|||True, I was just pointing out what I believed to be the switch set.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:kgi6c3tnrunn3qg16i9e78v8ril5jinuf6@.4ax.com...
> On Wed, 15 Aug 2007 13:38:02 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>>Actually I think they set the MAXDOP and not the affinity but from the
>>lack
>>of solid information it is hard to say for sure. We don't even know what
>>version he is running. But I think in SQL2000 there was a drop down for
>>the
>># of procs to use for parallelism (MAXDOP) and affinity was checkboxes.
> Well changing MAXDOP is unlikely to help keep OLTP go any quicker. It
> might help some complex long running query.
> Roy Harvey
> Beacon Falls, CT|||On Wed, 15 Aug 2007 20:03:19 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>>Actually I think they set the MAXDOP and not the affinity but from the
>>lack
>>of solid information it is hard to say for sure. We don't even know what
>>version he is running. But I think in SQL2000 there was a drop down for
>>the
>># of procs to use for parallelism (MAXDOP) and affinity was checkboxes.
>> Well changing MAXDOP is unlikely to help keep OLTP go any quicker. It
>> might help some complex long running query.
>True, I was just pointing out what I believed to be the switch set.
And I was just trying to make the statement that if you were right,
everything I said earlier about improving performance was wrong.
Roy|||SQL Standard Version 2000 on Advanced Server 2000 - 6 gig ram with four
processors XEON 1.8 GHTZ, 4 GIG paging size. I couldn't say for sure if this
is a hyperthreaded model or whether it is actually four processors - I think
the former.
--
Regards,
Jamie
"Andrew J. Kelly" wrote:
> Actually I think they set the MAXDOP and not the affinity but from the lack
> of solid information it is hard to say for sure. We don't even know what
> version he is running. But I think in SQL2000 there was a drop down for the
> # of procs to use for parallelism (MAXDOP) and affinity was checkboxes.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:um96c3td030rrn9hgi6r17n6b3qi8dmc0f@.4ax.com...
> > 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
>|||All four processors are checked but the dropdown option button is checked for
"Specify the number of processors to use for parallel execution of queries"
and says "Use 1" Then there is another box bleow the drop down that allows
for "Minimum query plan threshold for considering queries for parallel
execution (cost estiimate):" and this also lists as 1. What I did not say
was that there is also an option box (alternative option) that allows for
"Use of all available processors". Seems like this is what we are aiming for
with this dialog. Roy is recommending that I select the option box that
says "use all available processors" and my question, though less clear
before, but repeated now is, "Can I expect any improvement by switching to
this all processor option. I think that Roy pointed out that this would be
true.
--
Regards,
Jamie
"Andrew J. Kelly" wrote:
> Actually I think they set the MAXDOP and not the affinity but from the lack
> of solid information it is hard to say for sure. We don't even know what
> version he is running. But I think in SQL2000 there was a drop down for the
> # of procs to use for parallelism (MAXDOP) and affinity was checkboxes.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:um96c3td030rrn9hgi6r17n6b3qi8dmc0f@.4ax.com...
> > 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
>|||That's interesting. SQL 2000 Std edition only supports 2GB of memory. Its
great to have some for the OS but 4GB is a bit much. I don't know what this
info is in response to but it doesn't answer any of our questions regarding
the MAXDOP. Why don't you post the output of sp_configure with the advanced
settings so we can see the MAXDOP & processor affinity options?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:77C056DA-6440-41B0-8A69-45007BC3C485@.microsoft.com...
> SQL Standard Version 2000 on Advanced Server 2000 - 6 gig ram with four
> processors XEON 1.8 GHTZ, 4 GIG paging size. I couldn't say for sure if
> this
> is a hyperthreaded model or whether it is actually four processors - I
> think
> the former.
> --
> Regards,
> Jamie
>
> "Andrew J. Kelly" wrote:
>> Actually I think they set the MAXDOP and not the affinity but from the
>> lack
>> of solid information it is hard to say for sure. We don't even know what
>> version he is running. But I think in SQL2000 there was a drop down for
>> the
>> # of procs to use for parallelism (MAXDOP) and affinity was checkboxes.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
>> news:um96c3td030rrn9hgi6r17n6b3qi8dmc0f@.4ax.com...
>> > 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
>>|||OK that's better info. That means your MAXDOP is set to 1 so only there
will be no parallelism. But you are still able to use all 4 processors for
SQL Server, just not all by any 1 user at a time. It appears they changed
the threshold for parallelism from the default as well but with MAXDOP set
to 1 it makes no difference anyway. If you want to add parallelism back into
the mix you need to bump up the MAXDOP and see what happens. There is no way
to tell if this will increase or decrease performance without knowing in
detail what kind of operations you do, how many concurrent queries, how busy
the procs are etc. So it will be a trial and error thing. I would start by
changing it to 2 and see how it goes. Parallel processing may or may not
increase performance from a single users perspective who happens to be
running at the time. But it can decrease concurrency and have an overall
impact if this is a busy system. But in any case you should put the
threshold back to the default of which I think may have been 5 on SQL2000
but don't quote me on it.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:73AC1D8A-E05A-469B-B65B-4CF0E5E8DACA@.microsoft.com...
> All four processors are checked but the dropdown option button is checked
> for
> "Specify the number of processors to use for parallel execution of
> queries"
> and says "Use 1" Then there is another box bleow the drop down that
> allows
> for "Minimum query plan threshold for considering queries for parallel
> execution (cost estiimate):" and this also lists as 1. What I did not say
> was that there is also an option box (alternative option) that allows for
> "Use of all available processors". Seems like this is what we are aiming
> for
> with this dialog. Roy is recommending that I select the option box that
> says "use all available processors" and my question, though less clear
> before, but repeated now is, "Can I expect any improvement by switching to
> this all processor option. I think that Roy pointed out that this would
> be
> true.
> --
> Regards,
> Jamie
>
> "Andrew J. Kelly" wrote:
>> Actually I think they set the MAXDOP and not the affinity but from the
>> lack
>> of solid information it is hard to say for sure. We don't even know what
>> version he is running. But I think in SQL2000 there was a drop down for
>> the
>> # of procs to use for parallelism (MAXDOP) and affinity was checkboxes.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
>> news:um96c3td030rrn9hgi6r17n6b3qi8dmc0f@.4ax.com...
>> > 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
>>|||So if I understand correctly, I do not want to change the system over to use
the "all processor option", but instead, try to inch the parallel processing
upward. I'm not sure if I mentioned above that for the most part we are
heavily oltp but there are some reports that do need to come out of the live
system that relate to partner information and these frequently eat up
processing. Everything is already tuned for maximum concurrency with select
queries using the nolock option. The four gig of extra RAM is probably
useless although there is a four gig disk cache available which I think has
some benefits. I think this is the last performance tuning that will come
on this server and frankly, I thought the answer was to switch to the all
processor option. Instead, you are suggesting I change the MAXDOP to 2 for a
while and see how that goes.
--
Regards,
Jamie
"Andrew J. Kelly" wrote:
> OK that's better info. That means your MAXDOP is set to 1 so only there
> will be no parallelism. But you are still able to use all 4 processors for
> SQL Server, just not all by any 1 user at a time. It appears they changed
> the threshold for parallelism from the default as well but with MAXDOP set
> to 1 it makes no difference anyway. If you want to add parallelism back into
> the mix you need to bump up the MAXDOP and see what happens. There is no way
> to tell if this will increase or decrease performance without knowing in
> detail what kind of operations you do, how many concurrent queries, how busy
> the procs are etc. So it will be a trial and error thing. I would start by
> changing it to 2 and see how it goes. Parallel processing may or may not
> increase performance from a single users perspective who happens to be
> running at the time. But it can decrease concurrency and have an overall
> impact if this is a busy system. But in any case you should put the
> threshold back to the default of which I think may have been 5 on SQL2000
> but don't quote me on it.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:73AC1D8A-E05A-469B-B65B-4CF0E5E8DACA@.microsoft.com...
> > All four processors are checked but the dropdown option button is checked
> > for
> > "Specify the number of processors to use for parallel execution of
> > queries"
> > and says "Use 1" Then there is another box bleow the drop down that
> > allows
> > for "Minimum query plan threshold for considering queries for parallel
> > execution (cost estiimate):" and this also lists as 1. What I did not say
> > was that there is also an option box (alternative option) that allows for
> > "Use of all available processors". Seems like this is what we are aiming
> > for
> > with this dialog. Roy is recommending that I select the option box that
> > says "use all available processors" and my question, though less clear
> > before, but repeated now is, "Can I expect any improvement by switching to
> > this all processor option. I think that Roy pointed out that this would
> > be
> > true.
> > --
> > Regards,
> > Jamie
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Actually I think they set the MAXDOP and not the affinity but from the
> >> lack
> >> of solid information it is hard to say for sure. We don't even know what
> >> version he is running. But I think in SQL2000 there was a drop down for
> >> the
> >> # of procs to use for parallelism (MAXDOP) and affinity was checkboxes.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> >> news:um96c3td030rrn9hgi6r17n6b3qi8dmc0f@.4ax.com...
> >> > 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
> >>
> >>
>|||If you stick with turning off parallelism system-wide, you can still use the
OPTION (MAXDOP 0) for your report queries, which turns parallelism on for a
single query.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:E4D79E3D-BEAE-473F-8E68-4DA08ECC33EE@.microsoft.com...
So if I understand correctly, I do not want to change the system over to use
the "all processor option", but instead, try to inch the parallel processing
upward. I'm not sure if I mentioned above that for the most part we are
heavily oltp but there are some reports that do need to come out of the live
system that relate to partner information and these frequently eat up
processing. Everything is already tuned for maximum concurrency with
select
queries using the nolock option. The four gig of extra RAM is probably
useless although there is a four gig disk cache available which I think has
some benefits. I think this is the last performance tuning that will come
on this server and frankly, I thought the answer was to switch to the all
processor option. Instead, you are suggesting I change the MAXDOP to 2 for
a
while and see how that goes.
--
Regards,
Jamie
"Andrew J. Kelly" wrote:
> OK that's better info. That means your MAXDOP is set to 1 so only there
> will be no parallelism. But you are still able to use all 4 processors for
> SQL Server, just not all by any 1 user at a time. It appears they changed
> the threshold for parallelism from the default as well but with MAXDOP set
> to 1 it makes no difference anyway. If you want to add parallelism back
> into
> the mix you need to bump up the MAXDOP and see what happens. There is no
> way
> to tell if this will increase or decrease performance without knowing in
> detail what kind of operations you do, how many concurrent queries, how
> busy
> the procs are etc. So it will be a trial and error thing. I would start by
> changing it to 2 and see how it goes. Parallel processing may or may not
> increase performance from a single users perspective who happens to be
> running at the time. But it can decrease concurrency and have an overall
> impact if this is a busy system. But in any case you should put the
> threshold back to the default of which I think may have been 5 on SQL2000
> but don't quote me on it.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:73AC1D8A-E05A-469B-B65B-4CF0E5E8DACA@.microsoft.com...
> > All four processors are checked but the dropdown option button is
> > checked
> > for
> > "Specify the number of processors to use for parallel execution of
> > queries"
> > and says "Use 1" Then there is another box bleow the drop down that
> > allows
> > for "Minimum query plan threshold for considering queries for parallel
> > execution (cost estiimate):" and this also lists as 1. What I did not
> > say
> > was that there is also an option box (alternative option) that allows
> > for
> > "Use of all available processors". Seems like this is what we are
> > aiming
> > for
> > with this dialog. Roy is recommending that I select the option box
> > that
> > says "use all available processors" and my question, though less clear
> > before, but repeated now is, "Can I expect any improvement by switching
> > to
> > this all processor option. I think that Roy pointed out that this
> > would
> > be
> > true.
> > --
> > Regards,
> > Jamie
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Actually I think they set the MAXDOP and not the affinity but from the
> >> lack
> >> of solid information it is hard to say for sure. We don't even know
> >> what
> >> version he is running. But I think in SQL2000 there was a drop down for
> >> the
> >> # of procs to use for parallelism (MAXDOP) and affinity was checkboxes.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> >> news:um96c3td030rrn9hgi6r17n6b3qi8dmc0f@.4ax.com...
> >> > 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
> >>
> >>
>|||Yes in a multi-user system if you have poorly optimized queries or schemas
you can abuse parallelism and have one or two users monopolize the procs.
But as always it depends on a lot of factors. So the only way to know is to
try it and monitor to see which works best. Setting it to 4 (or 0 = same
thing) is usually not good for an OLTP system. Keep in mind that even though
you set this at more than 1 processor that does not mean SQL Server will use
more than 1. It takes into account several factors each time the query is
run to determine how many procs it will use. And this can be different each
time you execute that same query or procedure depending on the conditions in
the server at the time you run it. And by the way setting NOLOCK is NOT a
concurrency optimization. While it may have benefits under certain
conditions it should never be blindly used just because you have multiple
users.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:E4D79E3D-BEAE-473F-8E68-4DA08ECC33EE@.microsoft.com...
> So if I understand correctly, I do not want to change the system over to
> use
> the "all processor option", but instead, try to inch the parallel
> processing
> upward. I'm not sure if I mentioned above that for the most part we are
> heavily oltp but there are some reports that do need to come out of the
> live
> system that relate to partner information and these frequently eat up
> processing. Everything is already tuned for maximum concurrency with
> select
> queries using the nolock option. The four gig of extra RAM is probably
> useless although there is a four gig disk cache available which I think
> has
> some benefits. I think this is the last performance tuning that will
> come
> on this server and frankly, I thought the answer was to switch to the all
> processor option. Instead, you are suggesting I change the MAXDOP to 2
> for a
> while and see how that goes.
> --
> Regards,
> Jamie
>
> "Andrew J. Kelly" wrote:
>> OK that's better info. That means your MAXDOP is set to 1 so only there
>> will be no parallelism. But you are still able to use all 4 processors
>> for
>> SQL Server, just not all by any 1 user at a time. It appears they
>> changed
>> the threshold for parallelism from the default as well but with MAXDOP
>> set
>> to 1 it makes no difference anyway. If you want to add parallelism back
>> into
>> the mix you need to bump up the MAXDOP and see what happens. There is no
>> way
>> to tell if this will increase or decrease performance without knowing in
>> detail what kind of operations you do, how many concurrent queries, how
>> busy
>> the procs are etc. So it will be a trial and error thing. I would start
>> by
>> changing it to 2 and see how it goes. Parallel processing may or may not
>> increase performance from a single users perspective who happens to be
>> running at the time. But it can decrease concurrency and have an overall
>> impact if this is a busy system. But in any case you should put the
>> threshold back to the default of which I think may have been 5 on SQL2000
>> but don't quote me on it.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:73AC1D8A-E05A-469B-B65B-4CF0E5E8DACA@.microsoft.com...
>> > All four processors are checked but the dropdown option button is
>> > checked
>> > for
>> > "Specify the number of processors to use for parallel execution of
>> > queries"
>> > and says "Use 1" Then there is another box bleow the drop down that
>> > allows
>> > for "Minimum query plan threshold for considering queries for parallel
>> > execution (cost estiimate):" and this also lists as 1. What I did not
>> > say
>> > was that there is also an option box (alternative option) that allows
>> > for
>> > "Use of all available processors". Seems like this is what we are
>> > aiming
>> > for
>> > with this dialog. Roy is recommending that I select the option box
>> > that
>> > says "use all available processors" and my question, though less clear
>> > before, but repeated now is, "Can I expect any improvement by switching
>> > to
>> > this all processor option. I think that Roy pointed out that this
>> > would
>> > be
>> > true.
>> > --
>> > Regards,
>> > Jamie
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Actually I think they set the MAXDOP and not the affinity but from the
>> >> lack
>> >> of solid information it is hard to say for sure. We don't even know
>> >> what
>> >> version he is running. But I think in SQL2000 there was a drop down
>> >> for
>> >> the
>> >> # of procs to use for parallelism (MAXDOP) and affinity was
>> >> checkboxes.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >> Solid Quality Mentors
>> >>
>> >>
>> >> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
>> >> news:um96c3td030rrn9hgi6r17n6b3qi8dmc0f@.4ax.com...
>> >> > 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
>> >>
>> >>
>>|||Just one small point... your system was already configured for SQL
Server to use all processors. The limitation that was in place
stopped any single SQL command from using more than one, but when
multiple commands were run at the same time multiple processors could
be involved.
Since the only change you can make is to allow a single SQL command to
use multiple processors the odds of seeing major performance
improvement are not that high. You could find some long-running
process runs quicker however.
Roy Harvey
Beacon Falls, CT|||A couple of other small points:
If you have hyperthreading, you want to avoid MAXDOP 0. Rather, you want to
limit MAXDOP to the number of cores.
If you have a mixture of OLTP and DW databases on the same instance, then
turning off parallelism system-wide hurts one while enhancing the other.
You may be forced to put DB's of one type in one instance. Failing that,
you may have to pick through your code and add a MAXDOP hint where
applicable. In that case, you can use the Profiler to determine which
specific queries actually require it.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:l27bc35lhkjdmhrlruiu14o6cppf6o6fnk@.4ax.com...
Just one small point... your system was already configured for SQL
Server to use all processors. The limitation that was in place
stopped any single SQL command from using more than one, but when
multiple commands were run at the same time multiple processors could
be involved.
Since the only change you can make is to allow a single SQL command to
use multiple processors the odds of seeing major performance
improvement are not that high. You could find some long-running
process runs quicker however.
Roy Harvey
Beacon Falls, CT|||So probably the better approach is to nail down the long running queries and
use the with (MAXDOP) option in the query itself? [OPTION (MAXDOP 0) ] I am
not sure what a query such as this might look like.
--
Regards,
Jamie
"Andrew J. Kelly" wrote:
> Yes in a multi-user system if you have poorly optimized queries or schemas
> you can abuse parallelism and have one or two users monopolize the procs.
> But as always it depends on a lot of factors. So the only way to know is to
> try it and monitor to see which works best. Setting it to 4 (or 0 = same
> thing) is usually not good for an OLTP system. Keep in mind that even though
> you set this at more than 1 processor that does not mean SQL Server will use
> more than 1. It takes into account several factors each time the query is
> run to determine how many procs it will use. And this can be different each
> time you execute that same query or procedure depending on the conditions in
> the server at the time you run it. And by the way setting NOLOCK is NOT a
> concurrency optimization. While it may have benefits under certain
> conditions it should never be blindly used just because you have multiple
> users.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:E4D79E3D-BEAE-473F-8E68-4DA08ECC33EE@.microsoft.com...
> > So if I understand correctly, I do not want to change the system over to
> > use
> > the "all processor option", but instead, try to inch the parallel
> > processing
> > upward. I'm not sure if I mentioned above that for the most part we are
> > heavily oltp but there are some reports that do need to come out of the
> > live
> > system that relate to partner information and these frequently eat up
> > processing. Everything is already tuned for maximum concurrency with
> > select
> > queries using the nolock option. The four gig of extra RAM is probably
> > useless although there is a four gig disk cache available which I think
> > has
> > some benefits. I think this is the last performance tuning that will
> > come
> > on this server and frankly, I thought the answer was to switch to the all
> > processor option. Instead, you are suggesting I change the MAXDOP to 2
> > for a
> > while and see how that goes.
> > --
> > Regards,
> > Jamie
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> OK that's better info. That means your MAXDOP is set to 1 so only there
> >> will be no parallelism. But you are still able to use all 4 processors
> >> for
> >> SQL Server, just not all by any 1 user at a time. It appears they
> >> changed
> >> the threshold for parallelism from the default as well but with MAXDOP
> >> set
> >> to 1 it makes no difference anyway. If you want to add parallelism back
> >> into
> >> the mix you need to bump up the MAXDOP and see what happens. There is no
> >> way
> >> to tell if this will increase or decrease performance without knowing in
> >> detail what kind of operations you do, how many concurrent queries, how
> >> busy
> >> the procs are etc. So it will be a trial and error thing. I would start
> >> by
> >> changing it to 2 and see how it goes. Parallel processing may or may not
> >> increase performance from a single users perspective who happens to be
> >> running at the time. But it can decrease concurrency and have an overall
> >> impact if this is a busy system. But in any case you should put the
> >> threshold back to the default of which I think may have been 5 on SQL2000
> >> but don't quote me on it.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> news:73AC1D8A-E05A-469B-B65B-4CF0E5E8DACA@.microsoft.com...
> >> > All four processors are checked but the dropdown option button is
> >> > checked
> >> > for
> >> > "Specify the number of processors to use for parallel execution of
> >> > queries"
> >> > and says "Use 1" Then there is another box bleow the drop down that
> >> > allows
> >> > for "Minimum query plan threshold for considering queries for parallel
> >> > execution (cost estiimate):" and this also lists as 1. What I did not
> >> > say
> >> > was that there is also an option box (alternative option) that allows
> >> > for
> >> > "Use of all available processors". Seems like this is what we are
> >> > aiming
> >> > for
> >> > with this dialog. Roy is recommending that I select the option box
> >> > that
> >> > says "use all available processors" and my question, though less clear
> >> > before, but repeated now is, "Can I expect any improvement by switching
> >> > to
> >> > this all processor option. I think that Roy pointed out that this
> >> > would
> >> > be
> >> > true.
> >> > --
> >> > Regards,
> >> > Jamie
> >> >
> >> >
> >> > "Andrew J. Kelly" wrote:
> >> >
> >> >> Actually I think they set the MAXDOP and not the affinity but from the
> >> >> lack
> >> >> of solid information it is hard to say for sure. We don't even know
> >> >> what
> >> >> version he is running. But I think in SQL2000 there was a drop down
> >> >> for
> >> >> the
> >> >> # of procs to use for parallelism (MAXDOP) and affinity was
> >> >> checkboxes.
> >> >>
> >> >> --
> >> >> Andrew J. Kelly SQL MVP
> >> >> Solid Quality Mentors
> >> >>
> >> >>
> >> >> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> >> >> news:um96c3td030rrn9hgi6r17n6b3qi8dmc0f@.4ax.com...
> >> >> > 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
> >> >>
> >> >>
> >>
> >>
>|||The queries that would need the MAXDOP option are sorely obvious to us.
There is no doubt which queries slow down OLTP. I have never seen a query
sample using MAXDOP.
--
Regards,
Jamie
"Tom Moreau" wrote:
> A couple of other small points:
> If you have hyperthreading, you want to avoid MAXDOP 0. Rather, you want to
> limit MAXDOP to the number of cores.
> If you have a mixture of OLTP and DW databases on the same instance, then
> turning off parallelism system-wide hurts one while enhancing the other.
> You may be forced to put DB's of one type in one instance. Failing that,
> you may have to pick through your code and add a MAXDOP hint where
> applicable. In that case, you can use the Profiler to determine which
> specific queries actually require it.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:l27bc35lhkjdmhrlruiu14o6cppf6o6fnk@.4ax.com...
> Just one small point... your system was already configured for SQL
> Server to use all processors. The limitation that was in place
> stopped any single SQL command from using more than one, but when
> multiple commands were run at the same time multiple processors could
> be involved.
> Since the only change you can make is to allow a single SQL command to
> use multiple processors the odds of seeing major performance
> improvement are not that high. You could find some long-running
> process runs quicker however.
> Roy Harvey
> Beacon Falls, CT
>|||Well the best approach is to tune the queries first. But during testing you
can prove that using parallelism will indeed speed up the queries
significantly you can add the OPTION MAXDOP hint to those queries. But again
I would start smaller with something like 2 first. You don't want those
poorly optimized queries to monopolize the server needlessly. BOL has
examples but the syntax is pretty easy. At the end of the query just add the
hint such as:
SELECT * FROM Table WHERE X = 1 OPTION (MAXDOP 2);
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:E994E0DE-54D4-4840-B2FA-86488359BF58@.microsoft.com...
> So probably the better approach is to nail down the long running queries
> and
> use the with (MAXDOP) option in the query itself? [OPTION (MAXDOP 0) ] I
> am
> not sure what a query such as this might look like.
> --
> Regards,
> Jamie
>
> "Andrew J. Kelly" wrote:
>> Yes in a multi-user system if you have poorly optimized queries or
>> schemas
>> you can abuse parallelism and have one or two users monopolize the procs.
>> But as always it depends on a lot of factors. So the only way to know is
>> to
>> try it and monitor to see which works best. Setting it to 4 (or 0 = same
>> thing) is usually not good for an OLTP system. Keep in mind that even
>> though
>> you set this at more than 1 processor that does not mean SQL Server will
>> use
>> more than 1. It takes into account several factors each time the query is
>> run to determine how many procs it will use. And this can be different
>> each
>> time you execute that same query or procedure depending on the conditions
>> in
>> the server at the time you run it. And by the way setting NOLOCK is NOT a
>> concurrency optimization. While it may have benefits under certain
>> conditions it should never be blindly used just because you have multiple
>> users.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:E4D79E3D-BEAE-473F-8E68-4DA08ECC33EE@.microsoft.com...
>> > So if I understand correctly, I do not want to change the system over
>> > to
>> > use
>> > the "all processor option", but instead, try to inch the parallel
>> > processing
>> > upward. I'm not sure if I mentioned above that for the most part we
>> > are
>> > heavily oltp but there are some reports that do need to come out of the
>> > live
>> > system that relate to partner information and these frequently eat up
>> > processing. Everything is already tuned for maximum concurrency with
>> > select
>> > queries using the nolock option. The four gig of extra RAM is probably
>> > useless although there is a four gig disk cache available which I think
>> > has
>> > some benefits. I think this is the last performance tuning that will
>> > come
>> > on this server and frankly, I thought the answer was to switch to the
>> > all
>> > processor option. Instead, you are suggesting I change the MAXDOP to 2
>> > for a
>> > while and see how that goes.
>> > --
>> > Regards,
>> > Jamie
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> OK that's better info. That means your MAXDOP is set to 1 so only
>> >> there
>> >> will be no parallelism. But you are still able to use all 4 processors
>> >> for
>> >> SQL Server, just not all by any 1 user at a time. It appears they
>> >> changed
>> >> the threshold for parallelism from the default as well but with MAXDOP
>> >> set
>> >> to 1 it makes no difference anyway. If you want to add parallelism
>> >> back
>> >> into
>> >> the mix you need to bump up the MAXDOP and see what happens. There is
>> >> no
>> >> way
>> >> to tell if this will increase or decrease performance without knowing
>> >> in
>> >> detail what kind of operations you do, how many concurrent queries,
>> >> how
>> >> busy
>> >> the procs are etc. So it will be a trial and error thing. I would
>> >> start
>> >> by
>> >> changing it to 2 and see how it goes. Parallel processing may or may
>> >> not
>> >> increase performance from a single users perspective who happens to be
>> >> running at the time. But it can decrease concurrency and have an
>> >> overall
>> >> impact if this is a busy system. But in any case you should put the
>> >> threshold back to the default of which I think may have been 5 on
>> >> SQL2000
>> >> but don't quote me on it.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >> Solid Quality Mentors
>> >>
>> >>
>> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> >> news:73AC1D8A-E05A-469B-B65B-4CF0E5E8DACA@.microsoft.com...
>> >> > All four processors are checked but the dropdown option button is
>> >> > checked
>> >> > for
>> >> > "Specify the number of processors to use for parallel execution of
>> >> > queries"
>> >> > and says "Use 1" Then there is another box bleow the drop down that
>> >> > allows
>> >> > for "Minimum query plan threshold for considering queries for
>> >> > parallel
>> >> > execution (cost estiimate):" and this also lists as 1. What I did
>> >> > not
>> >> > say
>> >> > was that there is also an option box (alternative option) that
>> >> > allows
>> >> > for
>> >> > "Use of all available processors". Seems like this is what we are
>> >> > aiming
>> >> > for
>> >> > with this dialog. Roy is recommending that I select the option box
>> >> > that
>> >> > says "use all available processors" and my question, though less
>> >> > clear
>> >> > before, but repeated now is, "Can I expect any improvement by
>> >> > switching
>> >> > to
>> >> > this all processor option. I think that Roy pointed out that this
>> >> > would
>> >> > be
>> >> > true.
>> >> > --
>> >> > Regards,
>> >> > Jamie
>> >> >
>> >> >
>> >> > "Andrew J. Kelly" wrote:
>> >> >
>> >> >> Actually I think they set the MAXDOP and not the affinity but from
>> >> >> the
>> >> >> lack
>> >> >> of solid information it is hard to say for sure. We don't even know
>> >> >> what
>> >> >> version he is running. But I think in SQL2000 there was a drop down
>> >> >> for
>> >> >> the
>> >> >> # of procs to use for parallelism (MAXDOP) and affinity was
>> >> >> checkboxes.
>> >> >>
>> >> >> --
>> >> >> Andrew J. Kelly SQL MVP
>> >> >> Solid Quality Mentors
>> >> >>
>> >> >>
>> >> >> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
>> >> >> news:um96c3td030rrn9hgi6r17n6b3qi8dmc0f@.4ax.com...
>> >> >> > 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
>> >> >>
>> >> >>
>> >>
>> >>
>>|||I'm hoping it will improve queries with high sort and bookmark lookup costs.
Is this on-track?
--
Regards,
Jamie
"JXStern" wrote:
> On Sat, 18 Aug 2007 04:52:01 -0700, thejamie
> <thejamie@.discussions.microsoft.com> wrote:
> >The queries that would need the MAXDOP option are sorely obvious to us.
> >There is no doubt which queries slow down OLTP. I have never seen a query
> >sample using MAXDOP.
> You mean it's obvious which ones run a long time, that is not
> necessarily the same as the ones that would benefit from more
> processors.
> select * from tblFoo
> option (maxdop 1)
> J.
>|||I wouldn't hope too much. Bookmarks are single threaded since you can only
lookup one row at a time.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:F0A992DE-49CA-41E2-8F29-31CB82826C40@.microsoft.com...
> I'm hoping it will improve queries with high sort and bookmark lookup
> costs.
> Is this on-track?
> --
> Regards,
> Jamie
>
> "JXStern" wrote:
>> On Sat, 18 Aug 2007 04:52:01 -0700, thejamie
>> <thejamie@.discussions.microsoft.com> wrote:
>> >The queries that would need the MAXDOP option are sorely obvious to us.
>> >There is no doubt which queries slow down OLTP. I have never seen a
>> >query
>> >sample using MAXDOP.
>> You mean it's obvious which ones run a long time, that is not
>> necessarily the same as the ones that would benefit from more
>> processors.
>> select * from tblFoo
>> option (maxdop 1)
>> J.
>>|||New server reads as follows:
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 0 0
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 5 5
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 2147483647 2147483647
max text repl size (B) 0 2147483647 65536 65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
scan for startup procs 0 1 1 1
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0
Regards,
Jamie
"Andrew J. Kelly" wrote:
> That's interesting. SQL 2000 Std edition only supports 2GB of memory. Its
> great to have some for the OS but 4GB is a bit much. I don't know what this
> info is in response to but it doesn't answer any of our questions regarding
> the MAXDOP. Why don't you post the output of sp_configure with the advanced
> settings so we can see the MAXDOP & processor affinity options?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:77C056DA-6440-41B0-8A69-45007BC3C485@.microsoft.com...
> > SQL Standard Version 2000 on Advanced Server 2000 - 6 gig ram with four
> > processors XEON 1.8 GHTZ, 4 GIG paging size. I couldn't say for sure if
> > this
> > is a hyperthreaded model or whether it is actually four processors - I
> > think
> > the former.
> > --
> > Regards,
> > Jamie
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Actually I think they set the MAXDOP and not the affinity but from the
> >> lack
> >> of solid information it is hard to say for sure. We don't even know what
> >> version he is running. But I think in SQL2000 there was a drop down for
> >> the
> >> # of procs to use for parallelism (MAXDOP) and affinity was checkboxes.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> >> news:um96c3td030rrn9hgi6r17n6b3qi8dmc0f@.4ax.com...
> >> > 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
> >>
> >>
>|||This is correct. When we set this up on a new server, the MAXDOP
automatically set itself to 5. I had to drop it down because leaky
applications running against the server were getting mixed messages (service
apps) and they were launching multiple copies of themselves. Setting it to
one seems to have put this to a halt. It also slowed the system down.
--
Regards,
Jamie
"Andrew J. Kelly" wrote:
> OK that's better info. That means your MAXDOP is set to 1 so only there
> will be no parallelism. But you are still able to use all 4 processors for
> SQL Server, just not all by any 1 user at a time. It appears they changed
> the threshold for parallelism from the default as well but with MAXDOP set
> to 1 it makes no difference anyway. If you want to add parallelism back into
> the mix you need to bump up the MAXDOP and see what happens. There is no way
> to tell if this will increase or decrease performance without knowing in
> detail what kind of operations you do, how many concurrent queries, how busy
> the procs are etc. So it will be a trial and error thing. I would start by
> changing it to 2 and see how it goes. Parallel processing may or may not
> increase performance from a single users perspective who happens to be
> running at the time. But it can decrease concurrency and have an overall
> impact if this is a busy system. But in any case you should put the
> threshold back to the default of which I think may have been 5 on SQL2000
> but don't quote me on it.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:73AC1D8A-E05A-469B-B65B-4CF0E5E8DACA@.microsoft.com...
> > All four processors are checked but the dropdown option button is checked
> > for
> > "Specify the number of processors to use for parallel execution of
> > queries"
> > and says "Use 1" Then there is another box bleow the drop down that
> > allows
> > for "Minimum query plan threshold for considering queries for parallel
> > execution (cost estiimate):" and this also lists as 1. What I did not say
> > was that there is also an option box (alternative option) that allows for
> > "Use of all available processors". Seems like this is what we are aiming
> > for
> > with this dialog. Roy is recommending that I select the option box that
> > says "use all available processors" and my question, though less clear
> > before, but repeated now is, "Can I expect any improvement by switching to
> > this all processor option. I think that Roy pointed out that this would
> > be
> > true.
> > --
> > Regards,
> > Jamie
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Actually I think they set the MAXDOP and not the affinity but from the
> >> lack
> >> of solid information it is hard to say for sure. We don't even know what
> >> version he is running. But I think in SQL2000 there was a drop down for
> >> the
> >> # of procs to use for parallelism (MAXDOP) and affinity was checkboxes.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> >> news:um96c3td030rrn9hgi6r17n6b3qi8dmc0f@.4ax.com...
> >> > 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
> >>
> >>
>|||Make sure we are talking about the same thing here. MAXDOP defaults to 0 and
the Cost for Parallelism is 5. Leaving the cost for Parallelism to 5 is fine
but the MAXDOP should probably not be at 0 which is what the output of your
sp_configure says it is. I would change that to 1 and see if that helps.
sp_configure 'max degree of parallelism' , 1
RECONFIGURE
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:A9D95E02-08E5-40B3-8E87-EEE6176ABBC2@.microsoft.com...
> This is correct. When we set this up on a new server, the MAXDOP
> automatically set itself to 5. I had to drop it down because leaky
> applications running against the server were getting mixed messages
> (service
> apps) and they were launching multiple copies of themselves. Setting it
> to
> one seems to have put this to a halt. It also slowed the system down.
> --
> Regards,
> Jamie
>
> "Andrew J. Kelly" wrote:
>> OK that's better info. That means your MAXDOP is set to 1 so only there
>> will be no parallelism. But you are still able to use all 4 processors
>> for
>> SQL Server, just not all by any 1 user at a time. It appears they
>> changed
>> the threshold for parallelism from the default as well but with MAXDOP
>> set
>> to 1 it makes no difference anyway. If you want to add parallelism back
>> into
>> the mix you need to bump up the MAXDOP and see what happens. There is no
>> way
>> to tell if this will increase or decrease performance without knowing in
>> detail what kind of operations you do, how many concurrent queries, how
>> busy
>> the procs are etc. So it will be a trial and error thing. I would start
>> by
>> changing it to 2 and see how it goes. Parallel processing may or may not
>> increase performance from a single users perspective who happens to be
>> running at the time. But it can decrease concurrency and have an overall
>> impact if this is a busy system. But in any case you should put the
>> threshold back to the default of which I think may have been 5 on SQL2000
>> but don't quote me on it.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:73AC1D8A-E05A-469B-B65B-4CF0E5E8DACA@.microsoft.com...
>> > All four processors are checked but the dropdown option button is
>> > checked
>> > for
>> > "Specify the number of processors to use for parallel execution of
>> > queries"
>> > and says "Use 1" Then there is another box bleow the drop down that
>> > allows
>> > for "Minimum query plan threshold for considering queries for parallel
>> > execution (cost estiimate):" and this also lists as 1. What I did not
>> > say
>> > was that there is also an option box (alternative option) that allows
>> > for
>> > "Use of all available processors". Seems like this is what we are
>> > aiming
>> > for
>> > with this dialog. Roy is recommending that I select the option box
>> > that
>> > says "use all available processors" and my question, though less clear
>> > before, but repeated now is, "Can I expect any improvement by switching
>> > to
>> > this all processor option. I think that Roy pointed out that this
>> > would
>> > be
>> > true.
>> > --
>> > Regards,
>> > Jamie
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Actually I think they set the MAXDOP and not the affinity but from the
>> >> lack
>> >> of solid information it is hard to say for sure. We don't even know
>> >> what
>> >> version he is running. But I think in SQL2000 there was a drop down
>> >> for
>> >> the
>> >> # of procs to use for parallelism (MAXDOP) and affinity was
>> >> checkboxes.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >> Solid Quality Mentors
>> >>
>> >>
>> >> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
>> >> news:um96c3td030rrn9hgi6r17n6b3qi8dmc0f@.4ax.com...
>> >> > 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
>> >>
>> >>
>>