Dual Quad Core - CPU settings?

I have a IBM Dual Quad Core CPU system running SQL 2000 and all
SP/Updates. I'm seeing a log of blocking because of a vendors app that I
have no control over, causes the CPU Cores to run around 50% during the
extended periods of blocking. System is using 3.2GB of the 4GB (Windows
2003 Std, SQL 2000 Std CPU Licenses) and SQL is set to not use CPU Core
0 (actually, it was set to use all cores, but it has not made any
performance difference by not allowing access to Core 0 - this is a
Domain Controller and normally shows 3% load across all cores).
Should I limit the access to Cores to just 4-7 (second CPU) or just Core
0/4 and not the others, or should I just allow all cores?
Also, with Win 2003 Std and SQL 2000 Std (CPU licenses), should I be
using AWE or PAE modes? I'm currently setup for the default startup
mode.
Thanks - sorry for the ramble, been a long night.
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)You really seem to have underpowered the system when it comes to memory
compared to CPU's. SQL 2000 Std edition can only use 2GB of memory and the
server only has 4GB total anyway. The server is obviously being used for
more than SQL if 3.2GB is being used. If you want more memory you need to
move to either Enterprise Edition of SQL Server or upgrade to 2005 Std. I
don't recall what Win2003 Std supports but I believe it is only 4GB so you
should consider upgrading that as well.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.21768b1c15ad1052989a7d@.adfree.Usenet.com...
>I have a IBM Dual Quad Core CPU system running SQL 2000 and all
> SP/Updates. I'm seeing a log of blocking because of a vendors app that I
> have no control over, causes the CPU Cores to run around 50% during the
> extended periods of blocking. System is using 3.2GB of the 4GB (Windows
> 2003 Std, SQL 2000 Std CPU Licenses) and SQL is set to not use CPU Core
> 0 (actually, it was set to use all cores, but it has not made any
> performance difference by not allowing access to Core 0 - this is a
> Domain Controller and normally shows 3% load across all cores).
> Should I limit the access to Cores to just 4-7 (second CPU) or just Core
> 0/4 and not the others, or should I just allow all cores?
> Also, with Win 2003 Std and SQL 2000 Std (CPU licenses), should I be
> using AWE or PAE modes? I'm currently setup for the default startup
> mode.
> Thanks - sorry for the ramble, been a long night.
>
> --
> Leythos
> - Igitur qui desiderat pacem, praeparet bellum.
> - Calling an illegal alien an "undocumented worker" is like calling a
> drug dealer an "unlicensed pharmacist"
> spam999free@.rrohio.com (remove 999 for proper email address)|||In article <OH#AjM1CIHA.1168@.TK2MSFTNGP02.phx.gbl>,
sqlmvpnooospam@.shadhawk.com says...
> You really seem to have underpowered the system when it comes to memory
> compared to CPU's. SQL 2000 Std edition can only use 2GB of memory and the
> server only has 4GB total anyway. The server is obviously being used for
> more than SQL if 3.2GB is being used. If you want more memory you need to
> move to either Enterprise Edition of SQL Server or upgrade to 2005 Std. I
> don't recall what Win2003 Std supports but I believe it is only 4GB so you
> should consider upgrading that as well.
Yea, I'm stuck - the database app vendor suggested a single CPU, and we
moved from a Dual Xeon 2Ghz machine to this Dual Quad Core system and
believed it would help.
I bought Sql 2005 and CPU licenses and then downgraded to SQL 2000
because the app didn't convert to 2005 (tried to do a restore and it
didn't go well - schema security screwed it).
So, I'm stuck with bad stored proc's and block each other, which is
where I'm sure the real issue lies, but I don't have rights/permission
to change the vendors code.
In the old days there was speculation that Hyper Threading cause the
system to run slower, but, in testing I was seeing about 30% increase
using HT enabled under Xeon CPU's on a quality server. So, I've got Dual
Quad Core CPU's now, and I don't have the option to play - so I was
wondering if I should be only using some cores or just the second CPU's
cores (instead of all 0-7 cores, use cores 4-7 so that I'm hitting just
the one CPU)...
What about AWE/PAE modes on Win 2003 Std with SQL 2000 Std?
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||You should not have an issue with allowing SQL Server to use all the
available cores. But make sure to set the MAXDOP to something less than max.
I would start with 4 or maybe even 2. When you upgraded to 2005 did you try
the db in 80 compatibility mode? That can make a difference in app
compatibility or not. PAE is ignored on Win2003 Std and SQL2000 Std will
only ever use 2GB regardless of the OS. SQL2005 will use all that the OS can
offer.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.2176da90b52cdb30989a7f@.adfree.Usenet.com...
> In article <OH#AjM1CIHA.1168@.TK2MSFTNGP02.phx.gbl>,
> sqlmvpnooospam@.shadhawk.com says...
>> You really seem to have underpowered the system when it comes to memory
>> compared to CPU's. SQL 2000 Std edition can only use 2GB of memory and
>> the
>> server only has 4GB total anyway. The server is obviously being used for
>> more than SQL if 3.2GB is being used. If you want more memory you need to
>> move to either Enterprise Edition of SQL Server or upgrade to 2005 Std. I
>> don't recall what Win2003 Std supports but I believe it is only 4GB so
>> you
>> should consider upgrading that as well.
> Yea, I'm stuck - the database app vendor suggested a single CPU, and we
> moved from a Dual Xeon 2Ghz machine to this Dual Quad Core system and
> believed it would help.
> I bought Sql 2005 and CPU licenses and then downgraded to SQL 2000
> because the app didn't convert to 2005 (tried to do a restore and it
> didn't go well - schema security screwed it).
> So, I'm stuck with bad stored proc's and block each other, which is
> where I'm sure the real issue lies, but I don't have rights/permission
> to change the vendors code.
> In the old days there was speculation that Hyper Threading cause the
> system to run slower, but, in testing I was seeing about 30% increase
> using HT enabled under Xeon CPU's on a quality server. So, I've got Dual
> Quad Core CPU's now, and I don't have the option to play - so I was
> wondering if I should be only using some cores or just the second CPU's
> cores (instead of all 0-7 cores, use cores 4-7 so that I'm hitting just
> the one CPU)...
> What about AWE/PAE modes on Win 2003 Std with SQL 2000 Std?
> --
> Leythos
> - Igitur qui desiderat pacem, praeparet bellum.
> - Calling an illegal alien an "undocumented worker" is like calling a
> drug dealer an "unlicensed pharmacist"
> spam999free@.rrohio.com (remove 999 for proper email address)|||In article <egVTKn2CIHA.1208@.TK2MSFTNGP05.phx.gbl>,
sqlmvpnooospam@.shadhawk.com says...
> You should not have an issue with allowing SQL Server to use all the
> available cores. But make sure to set the MAXDOP to something less than max.
> I would start with 4 or maybe even 2.
I hate to sound ignorant, but MAXDOP?
> When you upgraded to 2005 did you try
> the db in 80 compatibility mode? That can make a difference in app
> compatibility or not.
Yes, all DB were in 80 mode, but, the dang schema permissions cause
massive problems. The same Vendors app installed native on 2005 didn't
have a problem and I've done a few of them that work fine, but the ones
we've moved from 2000 to 2005 all have schema permission problems.
> PAE is ignored on Win2003 Std and SQL2000 Std will
> only ever use 2GB regardless of the OS. SQL2005 will use all that the OS can
> offer.
Yea, that's what I thought, SQL Store is using 1.75GB currently. I could
try limiting it to 1.4GB to free up memory for the server itself, since
it's a AD controller. I can't believe that it needs more than 1GB for
the store when all of the blocking is based on a single sproc that is
being called 600+ times by 4 users, it's an update of some type, but I
have not looked directly at their code, just the blocking showing all
the time.
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||As a note, I've set the Max Query Plan option to 3 from 5 and set max
memory from FULL to 1.4GB and CPU load has gone from 50% to 17% and
users are not reporting any loss of application performance - they are
not reporting any increase yet.
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||MAXDOP stands for MAX Degree of Parallelism. Please refer to BooksOnLine
for more details. I don't know what schema permission issues you had but I
am willing to bet they were minor and had to do with the fact the logins,
users and schemas has mismatches. If it works from scratch it should work
with an upgrade. Have a look at these to see if they help:
http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
Moving Users
http://support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://support.microsoft.com/?id=240872 How to Resolve Permission Issues
When a Database Is Moved Between SQL Servers
> Yea, that's what I thought, SQL Store is using 1.75GB currently. I could
> try limiting it to 1.4GB to free up memory for the server itself, since
> it's a AD controller.
You have at least 4GB right? SQL will only use 2GB max so why would you
want to limit it even more?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.2176ed223bb957aa989a83@.adfree.Usenet.com...
> In article <egVTKn2CIHA.1208@.TK2MSFTNGP05.phx.gbl>,
> sqlmvpnooospam@.shadhawk.com says...
>> You should not have an issue with allowing SQL Server to use all the
>> available cores. But make sure to set the MAXDOP to something less than
>> max.
>> I would start with 4 or maybe even 2.
> I hate to sound ignorant, but MAXDOP?
>> When you upgraded to 2005 did you try
>> the db in 80 compatibility mode? That can make a difference in app
>> compatibility or not.
> Yes, all DB were in 80 mode, but, the dang schema permissions cause
> massive problems. The same Vendors app installed native on 2005 didn't
> have a problem and I've done a few of them that work fine, but the ones
> we've moved from 2000 to 2005 all have schema permission problems.
>> PAE is ignored on Win2003 Std and SQL2000 Std will
>> only ever use 2GB regardless of the OS. SQL2005 will use all that the OS
>> can
>> offer.
> Yea, that's what I thought, SQL Store is using 1.75GB currently. I could
> try limiting it to 1.4GB to free up memory for the server itself, since
> it's a AD controller. I can't believe that it needs more than 1GB for
> the store when all of the blocking is based on a single sproc that is
> being called 600+ times by 4 users, it's an update of some type, but I
> have not looked directly at their code, just the blocking showing all
> the time.
> --
> Leythos
> - Igitur qui desiderat pacem, praeparet bellum.
> - Calling an illegal alien an "undocumented worker" is like calling a
> drug dealer an "unlicensed pharmacist"
> spam999free@.rrohio.com (remove 999 for proper email address)|||In article <#M0PNZ3CIHA.4584@.TK2MSFTNGP03.phx.gbl>,
sqlmvpnooospam@.shadhawk.com says...
> MAXDOP stands for MAX Degree of Parallelism. Please refer to BooksOnLine
> for more details. I don't know what schema permission issues you had but I
> am willing to bet they were minor and had to do with the fact the logins,
> users and schemas has mismatches. If it works from scratch it should work
> with an upgrade. Have a look at these to see if they help:
> http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
> Moving Users
> http://support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
> Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
> http://support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://support.microsoft.com/?id=240872 How to Resolve Permission Issues
> When a Database Is Moved Between SQL Servers
> > Yea, that's what I thought, SQL Store is using 1.75GB currently. I could
> > try limiting it to 1.4GB to free up memory for the server itself, since
> > it's a AD controller.
> You have at least 4GB right? SQL will only use 2GB max so why would you
> want to limit it even more?
Yes, IBM 3800 series with 4GB RAM and 4 additional used as fail-over
RAM. I've never seen the RAM in use exceed 3.3GB, in fact I've never
seen a Windows Std server use more than 3.3GB on anything.
I was worried about the 2GB limit for apps and wondered if allowing it
to run (auto) at 1.78GB left enough for the OS and server functions -
that's why I limited it to 1.4GB - this was a shotgun move (sorry, I
know it's not professional, but sometimes you have to just try
something).
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||> Yes, IBM 3800 series with 4GB RAM and 4 additional used as fail-over
> RAM. I've never seen the RAM in use exceed 3.3GB, in fact I've never
> seen a Windows Std server use more than 3.3GB on anything.
Some motherboards or chipsets will not use all 4GB as you would expect. They
simply can't address 4GB or they will hide some of the memory to be used for
specific memory calls such as onboard video etc. I suspect that is what you
are seeing here and in reality you are using all the available memory. You
are most likely paging to disk a lot as well. I would find out what else
other than SQL Server is using the memory and see if you can address that.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.2176fd277dd6df39989a88@.adfree.Usenet.com...
> In article <#M0PNZ3CIHA.4584@.TK2MSFTNGP03.phx.gbl>,
> sqlmvpnooospam@.shadhawk.com says...
>> MAXDOP stands for MAX Degree of Parallelism. Please refer to BooksOnLine
>> for more details. I don't know what schema permission issues you had but
>> I
>> am willing to bet they were minor and had to do with the fact the logins,
>> users and schemas has mismatches. If it works from scratch it should work
>> with an upgrade. Have a look at these to see if they help:
>> http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
>> Moving Users
>> http://support.microsoft.com/?id=246133 How To Transfer Logins and
>> Passwords Between SQL Servers
>> http://support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
>> Restore
>> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
>> users
>> http://support.microsoft.com/?id=168001 User Logon and/or Permission
>> Errors After Restoring Dump
>> http://support.microsoft.com/?id=240872 How to Resolve Permission
>> Issues
>> When a Database Is Moved Between SQL Servers
>> > Yea, that's what I thought, SQL Store is using 1.75GB currently. I
>> > could
>> > try limiting it to 1.4GB to free up memory for the server itself, since
>> > it's a AD controller.
>> You have at least 4GB right? SQL will only use 2GB max so why would you
>> want to limit it even more?
> Yes, IBM 3800 series with 4GB RAM and 4 additional used as fail-over
> RAM. I've never seen the RAM in use exceed 3.3GB, in fact I've never
> seen a Windows Std server use more than 3.3GB on anything.
> I was worried about the 2GB limit for apps and wondered if allowing it
> to run (auto) at 1.78GB left enough for the OS and server functions -
> that's why I limited it to 1.4GB - this was a shotgun move (sorry, I
> know it's not professional, but sometimes you have to just try
> something).
> --
> Leythos
> - Igitur qui desiderat pacem, praeparet bellum.
> - Calling an illegal alien an "undocumented worker" is like calling a
> drug dealer an "unlicensed pharmacist"
> spam999free@.rrohio.com (remove 999 for proper email address)|||In article <MPG.21768b1c15ad1052989a7d@.adfree.Usenet.com>,
void@.nowhere.lan says...
> I have a IBM Dual Quad Core CPU system running SQL 2000 and all
> SP/Updates. I'm seeing a log of blocking because of a vendors app that I
> have no control over, causes the CPU Cores to run around 50% during the
> extended periods of blocking. System is using 3.2GB of the 4GB (Windows
> 2003 Std, SQL 2000 Std CPU Licenses) and SQL is set to not use CPU Core
> 0 (actually, it was set to use all cores, but it has not made any
> performance difference by not allowing access to Core 0 - this is a
> Domain Controller and normally shows 3% load across all cores).
> Should I limit the access to Cores to just 4-7 (second CPU) or just Core
> 0/4 and not the others, or should I just allow all cores?
> Also, with Win 2003 Std and SQL 2000 Std (CPU licenses), should I be
> using AWE or PAE modes? I'm currently setup for the default startup
> mode.
> Thanks - sorry for the ramble, been a long night.
Well, it's been a full day and I've found that changing the setting from
5 to 3 made all the difference in the world. So, a crappy coded Proc
(that I have no control over), caused massive blocking and now, it's
fine again.
I should point out that a typical back of this server took 6+ hours
normally, now it takes under 3 hours. Users have found a little
improvement in performance (DB App), but the server is no longer showing
a load and all functions are responsive.
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||Something doesn't connect. Typically, if you see massive blocking, your CPU
consunption should go down because these processes are being blocked and are
not doing anything. And I'd expect to see CPU usage go up when you have
resolved blocking.
Linchi
"Leythos" wrote:
> As a note, I've set the Max Query Plan option to 3 from 5 and set max
> memory from FULL to 1.4GB and CPU load has gone from 50% to 17% and
> users are not reporting any loss of application performance - they are
> not reporting any increase yet.
>
> --
> Leythos
> - Igitur qui desiderat pacem, praeparet bellum.
> - Calling an illegal alien an "undocumented worker" is like calling a
> drug dealer an "unlicensed pharmacist"
> spam999free@.rrohio.com (remove 999 for proper email address)
>|||In article <E7A06FE9-A2D7-433F-9DC4-9AF34EA25F2B@.microsoft.com>,
LinchiShea@.discussions.microsoft.com says...
> Something doesn't connect. Typically, if you see massive blocking, your CPU
> consunption should go down because these processes are being blocked and are
> not doing anything. And I'd expect to see CPU usage go up when you have
> resolved blocking.
The same 600+ updates now don't consume CPU time and don't cause
blocking that I can see (at least not during production periods where it
was causing blocking).
So, it's just looking good now.
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||What setting are you referring to that you changed from 5 to 3? As I have
said all along I think you have issues with MAXDOP and need to reduce the
parallelism.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.2176f66415ad1f61989a85@.adfree.Usenet.com...
> As a note, I've set the Max Query Plan option to 3 from 5 and set max
> memory from FULL to 1.4GB and CPU load has gone from 50% to 17% and
> users are not reporting any loss of application performance - they are
> not reporting any increase yet.
>
> --
> Leythos
> - Igitur qui desiderat pacem, praeparet bellum.
> - Calling an illegal alien an "undocumented worker" is like calling a
> drug dealer an "unlicensed pharmacist"
> spam999free@.rrohio.com (remove 999 for proper email address)|||In article <ObHjn3NDIHA.4360@.TK2MSFTNGP06.phx.gbl>,
sqlmvpnooospam@.shadhawk.com says...
> What setting are you referring to that you changed from 5 to 3? As I have
> said all along I think you have issues with MAXDOP and need to reduce the
> parallelism.
Yea, this is SQL 2000, and the max query plan option was set for 5,
changing it to 3 cleaned up the lack of proper coding from the vendors
app.
Thanks.
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||I don't have a 2000 instance to look at right now but I don't remember a
setting called that. Are you referring to the parallel query plan cost
option? If so then again I suggest you change the MAXDOP instead.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.2179ea0474b0e377989ac3@.adfree.Usenet.com...
> In article <ObHjn3NDIHA.4360@.TK2MSFTNGP06.phx.gbl>,
> sqlmvpnooospam@.shadhawk.com says...
>> What setting are you referring to that you changed from 5 to 3? As I
>> have
>> said all along I think you have issues with MAXDOP and need to reduce the
>> parallelism.
> Yea, this is SQL 2000, and the max query plan option was set for 5,
> changing it to 3 cleaned up the lack of proper coding from the vendors
> app.
> Thanks.
> --
> Leythos
> - Igitur qui desiderat pacem, praeparet bellum.
> - Calling an illegal alien an "undocumented worker" is like calling a
> drug dealer an "unlicensed pharmacist"
> spam999free@.rrohio.com (remove 999 for proper email address)|||In article <#nSn#ZbDIHA.5980@.TK2MSFTNGP04.phx.gbl>,
sqlmvpnooospam@.shadhawk.com says...
> I don't have a 2000 instance to look at right now but I don't remember a
> setting called that. Are you referring to the parallel query plan cost
> option? If so then again I suggest you change the MAXDOP instead.
I've looked all around and only see MAXDOP in SQL 2005 and as a query
hint when the option is specified.
I do not have permission to edit any of the vendors code, so I've used
the Memory/CPU settings tabs to adjust what can be run in parallel.
If you have a setting, in SQL 2000, that applies to ALL proc's without
having to edit any of the code, just changing SQL settings, then I would
love to see it - thanks.
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||MAXDOP is not a SQL2005 specific setting. It is there in 2000 as well. Again
I don't have an instance of 2000 so I am going from memory here but if you
right click on the server in EM and choose properties there should be a
Processor tab. On that tab there should be a dropdown box (I believe) to
choose the maximum number of processors that can be used in parallel. It
will be set to 0 by default. You can also change or see this in
sp_configure. It is the "max degree of parallelism" option. You can see more
details in BOL under max degree of parallelism. If you set it here it
affects everything on the server. You can also specify a hint at a statement
level but that is not what you want.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.217ac0fd1c33cfbc989ad6@.adfree.Usenet.com...
> In article <#nSn#ZbDIHA.5980@.TK2MSFTNGP04.phx.gbl>,
> sqlmvpnooospam@.shadhawk.com says...
>> I don't have a 2000 instance to look at right now but I don't remember a
>> setting called that. Are you referring to the parallel query plan cost
>> option? If so then again I suggest you change the MAXDOP instead.
> I've looked all around and only see MAXDOP in SQL 2005 and as a query
> hint when the option is specified.
> I do not have permission to edit any of the vendors code, so I've used
> the Memory/CPU settings tabs to adjust what can be run in parallel.
> If you have a setting, in SQL 2000, that applies to ALL proc's without
> having to edit any of the code, just changing SQL settings, then I would
> love to see it - thanks.
> --
> Leythos
> - Igitur qui desiderat pacem, praeparet bellum.
> - Calling an illegal alien an "undocumented worker" is like calling a
> drug dealer an "unlicensed pharmacist"
> spam999free@.rrohio.com (remove 999 for proper email address)|||In article <OxdtLgcDIHA.4752@.TK2MSFTNGP04.phx.gbl>,
sqlmvpnooospam@.shadhawk.com says...
> MAXDOP is not a SQL2005 specific setting. It is there in 2000 as well. Again
> I don't have an instance of 2000 so I am going from memory here but if you
> right click on the server in EM and choose properties there should be a
> Processor tab. On that tab there should be a dropdown box (I believe) to
> choose the maximum number of processors that can be used in parallel. It
> will be set to 0 by default. You can also change or see this in
> sp_configure. It is the "max degree of parallelism" option. You can see more
> details in BOL under max degree of parallelism. If you set it here it
> affects everything on the server. You can also specify a hint at a statement
> level but that is not what you want.
Thanks for your time - somehow I seem to have missed this when looking
in those areas. I will check again.
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||Just a small point, the hint is MAXDOP but the configuration setting
is called "max degree of parallelism". If type that into the SQL
Server 2000 Books On Line index you will find more information.
Roy Harvey
Beacon Falls, CT
On Mon, 15 Oct 2007 07:21:49 -0400, Leythos <void@.nowhere.lan> wrote:
>In article <OxdtLgcDIHA.4752@.TK2MSFTNGP04.phx.gbl>,
>sqlmvpnooospam@.shadhawk.com says...
>> MAXDOP is not a SQL2005 specific setting. It is there in 2000 as well. Again
>> I don't have an instance of 2000 so I am going from memory here but if you
>> right click on the server in EM and choose properties there should be a
>> Processor tab. On that tab there should be a dropdown box (I believe) to
>> choose the maximum number of processors that can be used in parallel. It
>> will be set to 0 by default. You can also change or see this in
>> sp_configure. It is the "max degree of parallelism" option. You can see more
>> details in BOL under max degree of parallelism. If you set it here it
>> affects everything on the server. You can also specify a hint at a statement
>> level but that is not what you want.
>Thanks for your time - somehow I seem to have missed this when looking
>in those areas. I will check again.