3G ram for SQL server: processors

Showing posts with label processors. Show all posts

Tuesday, March 6, 2012

64-bit SQL Server for Xeon

Hello,
Is there a SQL Server 2000 64-bit Edition for XEON processors?
I saw that there are two different versions of SQL 2005 (64 Bit Extended and
64 Bit Itanium), but for SQL 2000 I found only one, and it seems that it's
for Itanium processors.
Thank youHi
SQL Server 2000 has only a full 64 bit edition of Itaniums.
SQL Server 2000 with SP4 supports EM64T and x64.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Vi" <Vi@.discussions.microsoft.com> wrote in message
news:8B3CC9AB-8025-4CDB-9B1D-4F0AC5DFFB8E@.microsoft.com...
> Hello,
> Is there a SQL Server 2000 64-bit Edition for XEON processors?
> I saw that there are two different versions of SQL 2005 (64 Bit Extended
> and
> 64 Bit Itanium), but for SQL 2000 I found only one, and it seems that it's
> for Itanium processors.
> Thank you|||So how do I install SQL 64-bit on a Xeon processor based server? Is there a
release "SQL 2000 with SP4"? And if so, is 64-bit an option during
installation?
Thanks
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> SQL Server 2000 has only a full 64 bit edition of Itaniums.
> SQL Server 2000 with SP4 supports EM64T and x64.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Vi" <Vi@.discussions.microsoft.com> wrote in message
> news:8B3CC9AB-8025-4CDB-9B1D-4F0AC5DFFB8E@.microsoft.com...
> > Hello,
> > Is there a SQL Server 2000 64-bit Edition for XEON processors?
> > I saw that there are two different versions of SQL 2005 (64 Bit Extended
> > and
> > 64 Bit Itanium), but for SQL 2000 I found only one, and it seems that it's
> > for Itanium processors.
> >
> > Thank you
>
>|||"Vi" <Vi@.discussions.microsoft.com> wrote in message
news:A329E111-A514-4860-9697-64F947441955@.microsoft.com...
> So how do I install SQL 64-bit on a Xeon processor based server? Is there
> a
> release "SQL 2000 with SP4"? And if so, is 64-bit an option during
> installation?
>
It's 32-bit SQL Server 2000 running in WOW32 mode on 64-bit windows. You
get a 4gig user-mode address space because of WOW64, but it's still a 32-bit
application.
David

64bit SQL

Hi,
Can anyone confirm that the 64bit edition on SQL 2000 will only run on
Itanium 64 bit machines & not Xeon 64 bit processors?
Is there an Microsoft article on this?
Regards
SteveConfirmed.
(Don't know about MS article, haven't searched KB..)
On EM64T and 64 bit OS, you can run 32 bit SQL Server with sp4 in WOW, which is supported, but
probably not what you want.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<stevanflores@.gmail.com> wrote in message
news:1142414594.177552.235650@.z34g2000cwc.googlegroups.com...
> Hi,
> Can anyone confirm that the 64bit edition on SQL 2000 will only run on
> Itanium 64 bit machines & not Xeon 64 bit processors?
> Is there an Microsoft article on this?
> Regards
> Steve
>|||Hi
Yes , 64bit runs on Itanium , but I don't remember the specific article
about that
<stevanflores@.gmail.com> wrote in message
news:1142414594.177552.235650@.z34g2000cwc.googlegroups.com...
> Hi,
> Can anyone confirm that the 64bit edition on SQL 2000 will only run on
> Itanium 64 bit machines & not Xeon 64 bit processors?
> Is there an Microsoft article on this?
> Regards
> Steve
>|||Thank you both.
We have a 32 bit application that is supposed be getting installed onto
64bit Server 2003, seeing as that we are still going to be using 32bit
SQL 2000, do you think it would be better for us to use the 32bit
version of Windows 2003?
Regards,
Steve|||IAE64 supports fully 64 bit and partially 32. Before this there was x64
allowing both architectures. As far as I know benefits (64) are visible when
you have more than 4 Gb RAM or 16 processors.
--
current location: alicante (es)
"Stevan" wrote:
> Thank you both.
> We have a 32 bit application that is supposed be getting installed onto
> 64bit Server 2003, seeing as that we are still going to be using 32bit
> SQL 2000, do you think it would be better for us to use the 32bit
> version of Windows 2003?
> Regards,
> Steve
>|||You should really look into using SQL2005 since you can run the 64 bit
edition of 2005 on Win2003 X64. But if you stay with 2000 then the 32 bit
is the only choice and it will run just fine on X64.
--
Andrew J. Kelly SQL MVP
"Stevan" <stevanflores@.gmail.com> wrote in message
news:1142415651.698599.131360@.e56g2000cwe.googlegroups.com...
> Thank you both.
> We have a 32 bit application that is supposed be getting installed onto
> 64bit Server 2003, seeing as that we are still going to be using 32bit
> SQL 2000, do you think it would be better for us to use the 32bit
> version of Windows 2003?
> Regards,
> Steve
>|||You can certainly run SQL2000 SP4 on Windows 2003 x64. And there are some
benefits right away. For instance, SQL2000 gets to use 4GB by default.
Whether these benefits will bobble up to your app level is a different story.
The only way to determine whether using Windows 2003 x64 will provide better
performance for your app is for you to conduct some tests. For the workloads
I have tested, I get better performance on Windows 2003 x64 for some
workloads and no change in performance for the others >with Windows 2003 x86
vs x64 being the only variable<.
Linchi
"Stevan" wrote:
> Thank you both.
> We have a 32 bit application that is supposed be getting installed onto
> 64bit Server 2003, seeing as that we are still going to be using 32bit
> SQL 2000, do you think it would be better for us to use the 32bit
> version of Windows 2003?
> Regards,
> Steve
>|||stevanflores@.gmail.com wrote:
> Hi,
> Can anyone confirm that the 64bit edition on SQL 2000 will only run on
> Itanium 64 bit machines & not Xeon 64 bit processors?
> Is there an Microsoft article on this?
> Regards
> Steve
64-bit IA64 Itanium is your only 64-bit option for SQL Server 2000 and
it must run on equivalent Itanium hardware.
SQL Server 2005 supports three platforms: x86, x64 (AMD64 and Intel
EM64T), and Itanium
You can run SQL Server 2000 SP4 x86 and SQL Server 2005 x86 on Windows
Server 2003 x64 Edition.
David Gugick - SQL Server MVP
Quest Software

Saturday, February 11, 2012

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
>> >>
>> >>
>>

Tuesday, March 6, 2012

64-bit SQL Server for Xeon

64bit SQL

Saturday, February 11, 2012

4 prrocessors versus 1

4 prrocessors versus 1

4 prrocessors versus 1

3G ram for SQL server

Blog Archive

About Me