Hi again (and again) guys...
I have just read an article in the SQL Server magasine which says that its
recommanded to have 1 file by processor for the tempdb database.
does this recommandation is good for a data warehousing solution?
I'm suffering some disk issue and I have a lot of CXPacket wait types.
So I'm looking for solution to improve this...
but I can't do anything at the disk level.
My config:
Windows 2003 Ent.
SQL Server 2000 Ent.
4 Xeon 3.6Ghz with HT
4 Gb of RAM (/3Gb option set)
200 Gb on a SAN
I have some communication issue with the client I.T. Team, and I just know
that I have 200 Gb of disk, I have no idea about the disk config, cache
config etc...
I have no choice to use it.
I can't spread my files among dedicated disks (like moving log files, tempdb
database...)
the performance degrade specially when there is reading & writing activity
on the disk, like filling a table with the indexes in place or like doiong
an update on a "big" table (1 million of rows)
but complex select queries are slow too due to CXPacket waits.
I have added 7 files in for the tempdb database and 7 files for the staging
database.
For the moment I monitor the loading step which is slower then expected.
after this change the number of CXPacket wait types has increased from 10-20
waits to 150 waits!!!! (I'm using the sp_who1 cusomt procedure to see all
the wait type)
I have another client with near the same volume of data, a SAN and a smaller
hardware (2 cpu only, 2Gb of ram) and doing the same loading takes half of
the time!
I process 70 000rows / sec on this small server while I process 45
000rows/sec on my biggest but slower server...
I have a third client where the bottleneck is the disk controller, I reach
the maximum throughput of 80MB/s on it.
So our loading procedures are optimal for all client and the overral
solution is good except this one wherethesame procedures are slow.
what are my options?
The only answer I receive from the I.T. Team is: "The system is optimal, for
us there is no performance issue"
for us we clearly have issues on this server.
queries which generally takes 5 seconds to execute takes 35 seconds on this
server!
and we defrag ALL the indexes after every load.
thanks for your ideas if you have one :-)
Jerome.Jeje,
If the Average Queue Length is high then there is a disk bottle neck. The
disk subsystem may be optimal but still not fast enough for what you need.
Moving to more than one file in a filegroup on a multiprocessor server is
generally helpful. You've effectively increased the number of parrallel
tasks. Have you tried lowering your mdop?
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23PsrShk6FHA.3888@.TK2MSFTNGP10.phx.gbl...
> Hi again (and again) guys...
> I have just read an article in the SQL Server magasine which says that its
> recommanded to have 1 file by processor for the tempdb database.
> does this recommandation is good for a data warehousing solution?
> I'm suffering some disk issue and I have a lot of CXPacket wait types.
> So I'm looking for solution to improve this...
> but I can't do anything at the disk level.
> My config:
> Windows 2003 Ent.
> SQL Server 2000 Ent.
> 4 Xeon 3.6Ghz with HT
> 4 Gb of RAM (/3Gb option set)
> 200 Gb on a SAN
> I have some communication issue with the client I.T. Team, and I just know
> that I have 200 Gb of disk, I have no idea about the disk config, cache
> config etc...
> I have no choice to use it.
> I can't spread my files among dedicated disks (like moving log files,
> tempdb database...)
> the performance degrade specially when there is reading & writing activity
> on the disk, like filling a table with the indexes in place or like doiong
> an update on a "big" table (1 million of rows)
> but complex select queries are slow too due to CXPacket waits.
> I have added 7 files in for the tempdb database and 7 files for the
> staging database.
> For the moment I monitor the loading step which is slower then expected.
> after this change the number of CXPacket wait types has increased from
> 10-20 waits to 150 waits!!!! (I'm using the sp_who1 cusomt procedure to
> see all the wait type)
> I have another client with near the same volume of data, a SAN and a
> smaller hardware (2 cpu only, 2Gb of ram) and doing the same loading takes
> half of the time!
> I process 70 000rows / sec on this small server while I process 45
> 000rows/sec on my biggest but slower server...
> I have a third client where the bottleneck is the disk controller, I reach
> the maximum throughput of 80MB/s on it.
> So our loading procedures are optimal for all client and the overral
> solution is good except this one wherethesame procedures are slow.
> what are my options?
> The only answer I receive from the I.T. Team is: "The system is optimal,
> for us there is no performance issue"
> for us we clearly have issues on this server.
> queries which generally takes 5 seconds to execute takes 35 seconds on
> this server!
> and we defrag ALL the indexes after every load.
> thanks for your ideas if you have one :-)
> Jerome.
>|||Hi,
yes, I have played with the mdop option.
sometimes this improve the performance, sometimes this decrease the
performance.
for queries where I have "simple" joins and group by clause, I have less
issues then queries with outer join and lookup queires (queries statement
under the select statement select (select min(...) from tableA... ) as
mindate from tableB...))
The overall result of the entire process is a slower result when maxdop = 1.
update statements are always slow, and if I update a column with an index on
it... its really bad!
I have tried to add some files in my filegroups, but the result is not so
good, my staging queries generate 8 times more CXPacket locks.
I'll try some options and test it again.
if you have some other ideas, I'll take it ;-)
"Danny" <someone@.nowhere.com> wrote in message
news:91Gef.7467$vS4.4914@.trnddc01...
> Jeje,
> If the Average Queue Length is high then there is a disk bottle neck. The
> disk subsystem may be optimal but still not fast enough for what you need.
> Moving to more than one file in a filegroup on a multiprocessor server is
> generally helpful. You've effectively increased the number of parrallel
> tasks. Have you tried lowering your mdop?
>
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:%23PsrShk6FHA.3888@.TK2MSFTNGP10.phx.gbl...
>
Showing posts with label processor. Show all posts
Showing posts with label processor. Show all posts
Sunday, March 11, 2012
back again with my server performance issue...
Hi again (and again) guys...
I have just read an article in the SQL Server magasine which says that its
recommanded to have 1 file by processor for the tempdb database.
does this recommandation is good for a data warehousing solution?
I'm suffering some disk issue and I have a lot of CXPacket wait types.
So I'm looking for solution to improve this...
but I can't do anything at the disk level.
My config:
Windows 2003 Ent.
SQL Server 2000 Ent.
4 Xeon 3.6Ghz with HT
4 Gb of RAM (/3Gb option set)
200 Gb on a SAN
I have some communication issue with the client I.T. Team, and I just know
that I have 200 Gb of disk, I have no idea about the disk config, cache
config etc...
I have no choice to use it.
I can't spread my files among dedicated disks (like moving log files, tempdb
database...)
the performance degrade specially when there is reading & writing activity
on the disk, like filling a table with the indexes in place or like doiong
an update on a "big" table (1 million of rows)
but complex select queries are slow too due to CXPacket waits.
I have added 7 files in for the tempdb database and 7 files for the staging
database.
For the moment I monitor the loading step which is slower then expected.
after this change the number of CXPacket wait types has increased from 10-20
waits to 150 waits!!!! (I'm using the sp_who1 cusomt procedure to see all
the wait type)
I have another client with near the same volume of data, a SAN and a smaller
hardware (2 cpu only, 2Gb of ram) and doing the same loading takes half of
the time!
I process 70 000rows / sec on this small server while I process 45
000rows/sec on my biggest but slower server...
I have a third client where the bottleneck is the disk controller, I reach
the maximum throughput of 80MB/s on it.
So our loading procedures are optimal for all client and the overral
solution is good except this one wherethesame procedures are slow.
what are my options?
The only answer I receive from the I.T. Team is: "The system is optimal, for
us there is no performance issue"
for us we clearly have issues on this server.
queries which generally takes 5 seconds to execute takes 35 seconds on this
server!
and we defrag ALL the indexes after every load.
thanks for your ideas if you have one :-)
Jerome.
Jeje,
If the Average Queue Length is high then there is a disk bottle neck. The
disk subsystem may be optimal but still not fast enough for what you need.
Moving to more than one file in a filegroup on a multiprocessor server is
generally helpful. You've effectively increased the number of parrallel
tasks. Have you tried lowering your mdop?
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23PsrShk6FHA.3888@.TK2MSFTNGP10.phx.gbl...
> Hi again (and again) guys...
> I have just read an article in the SQL Server magasine which says that its
> recommanded to have 1 file by processor for the tempdb database.
> does this recommandation is good for a data warehousing solution?
> I'm suffering some disk issue and I have a lot of CXPacket wait types.
> So I'm looking for solution to improve this...
> but I can't do anything at the disk level.
> My config:
> Windows 2003 Ent.
> SQL Server 2000 Ent.
> 4 Xeon 3.6Ghz with HT
> 4 Gb of RAM (/3Gb option set)
> 200 Gb on a SAN
> I have some communication issue with the client I.T. Team, and I just know
> that I have 200 Gb of disk, I have no idea about the disk config, cache
> config etc...
> I have no choice to use it.
> I can't spread my files among dedicated disks (like moving log files,
> tempdb database...)
> the performance degrade specially when there is reading & writing activity
> on the disk, like filling a table with the indexes in place or like doiong
> an update on a "big" table (1 million of rows)
> but complex select queries are slow too due to CXPacket waits.
> I have added 7 files in for the tempdb database and 7 files for the
> staging database.
> For the moment I monitor the loading step which is slower then expected.
> after this change the number of CXPacket wait types has increased from
> 10-20 waits to 150 waits!!!! (I'm using the sp_who1 cusomt procedure to
> see all the wait type)
> I have another client with near the same volume of data, a SAN and a
> smaller hardware (2 cpu only, 2Gb of ram) and doing the same loading takes
> half of the time!
> I process 70 000rows / sec on this small server while I process 45
> 000rows/sec on my biggest but slower server...
> I have a third client where the bottleneck is the disk controller, I reach
> the maximum throughput of 80MB/s on it.
> So our loading procedures are optimal for all client and the overral
> solution is good except this one wherethesame procedures are slow.
> what are my options?
> The only answer I receive from the I.T. Team is: "The system is optimal,
> for us there is no performance issue"
> for us we clearly have issues on this server.
> queries which generally takes 5 seconds to execute takes 35 seconds on
> this server!
> and we defrag ALL the indexes after every load.
> thanks for your ideas if you have one :-)
> Jerome.
>
|||Hi,
yes, I have played with the mdop option.
sometimes this improve the performance, sometimes this decrease the
performance.
for queries where I have "simple" joins and group by clause, I have less
issues then queries with outer join and lookup queires (queries statement
under the select statement select (select min(...) from tableA... ) as
mindate from tableB...))
The overall result of the entire process is a slower result when maxdop = 1.
update statements are always slow, and if I update a column with an index on
it... its really bad!
I have tried to add some files in my filegroups, but the result is not so
good, my staging queries generate 8 times more CXPacket locks.
I'll try some options and test it again.
if you have some other ideas, I'll take it ;-)
"Danny" <someone@.nowhere.com> wrote in message
news:91Gef.7467$vS4.4914@.trnddc01...
> Jeje,
> If the Average Queue Length is high then there is a disk bottle neck. The
> disk subsystem may be optimal but still not fast enough for what you need.
> Moving to more than one file in a filegroup on a multiprocessor server is
> generally helpful. You've effectively increased the number of parrallel
> tasks. Have you tried lowering your mdop?
>
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:%23PsrShk6FHA.3888@.TK2MSFTNGP10.phx.gbl...
>
I have just read an article in the SQL Server magasine which says that its
recommanded to have 1 file by processor for the tempdb database.
does this recommandation is good for a data warehousing solution?
I'm suffering some disk issue and I have a lot of CXPacket wait types.
So I'm looking for solution to improve this...
but I can't do anything at the disk level.
My config:
Windows 2003 Ent.
SQL Server 2000 Ent.
4 Xeon 3.6Ghz with HT
4 Gb of RAM (/3Gb option set)
200 Gb on a SAN
I have some communication issue with the client I.T. Team, and I just know
that I have 200 Gb of disk, I have no idea about the disk config, cache
config etc...
I have no choice to use it.
I can't spread my files among dedicated disks (like moving log files, tempdb
database...)
the performance degrade specially when there is reading & writing activity
on the disk, like filling a table with the indexes in place or like doiong
an update on a "big" table (1 million of rows)
but complex select queries are slow too due to CXPacket waits.
I have added 7 files in for the tempdb database and 7 files for the staging
database.
For the moment I monitor the loading step which is slower then expected.
after this change the number of CXPacket wait types has increased from 10-20
waits to 150 waits!!!! (I'm using the sp_who1 cusomt procedure to see all
the wait type)
I have another client with near the same volume of data, a SAN and a smaller
hardware (2 cpu only, 2Gb of ram) and doing the same loading takes half of
the time!
I process 70 000rows / sec on this small server while I process 45
000rows/sec on my biggest but slower server...
I have a third client where the bottleneck is the disk controller, I reach
the maximum throughput of 80MB/s on it.
So our loading procedures are optimal for all client and the overral
solution is good except this one wherethesame procedures are slow.
what are my options?
The only answer I receive from the I.T. Team is: "The system is optimal, for
us there is no performance issue"
for us we clearly have issues on this server.
queries which generally takes 5 seconds to execute takes 35 seconds on this
server!
and we defrag ALL the indexes after every load.
thanks for your ideas if you have one :-)
Jerome.
Jeje,
If the Average Queue Length is high then there is a disk bottle neck. The
disk subsystem may be optimal but still not fast enough for what you need.
Moving to more than one file in a filegroup on a multiprocessor server is
generally helpful. You've effectively increased the number of parrallel
tasks. Have you tried lowering your mdop?
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23PsrShk6FHA.3888@.TK2MSFTNGP10.phx.gbl...
> Hi again (and again) guys...
> I have just read an article in the SQL Server magasine which says that its
> recommanded to have 1 file by processor for the tempdb database.
> does this recommandation is good for a data warehousing solution?
> I'm suffering some disk issue and I have a lot of CXPacket wait types.
> So I'm looking for solution to improve this...
> but I can't do anything at the disk level.
> My config:
> Windows 2003 Ent.
> SQL Server 2000 Ent.
> 4 Xeon 3.6Ghz with HT
> 4 Gb of RAM (/3Gb option set)
> 200 Gb on a SAN
> I have some communication issue with the client I.T. Team, and I just know
> that I have 200 Gb of disk, I have no idea about the disk config, cache
> config etc...
> I have no choice to use it.
> I can't spread my files among dedicated disks (like moving log files,
> tempdb database...)
> the performance degrade specially when there is reading & writing activity
> on the disk, like filling a table with the indexes in place or like doiong
> an update on a "big" table (1 million of rows)
> but complex select queries are slow too due to CXPacket waits.
> I have added 7 files in for the tempdb database and 7 files for the
> staging database.
> For the moment I monitor the loading step which is slower then expected.
> after this change the number of CXPacket wait types has increased from
> 10-20 waits to 150 waits!!!! (I'm using the sp_who1 cusomt procedure to
> see all the wait type)
> I have another client with near the same volume of data, a SAN and a
> smaller hardware (2 cpu only, 2Gb of ram) and doing the same loading takes
> half of the time!
> I process 70 000rows / sec on this small server while I process 45
> 000rows/sec on my biggest but slower server...
> I have a third client where the bottleneck is the disk controller, I reach
> the maximum throughput of 80MB/s on it.
> So our loading procedures are optimal for all client and the overral
> solution is good except this one wherethesame procedures are slow.
> what are my options?
> The only answer I receive from the I.T. Team is: "The system is optimal,
> for us there is no performance issue"
> for us we clearly have issues on this server.
> queries which generally takes 5 seconds to execute takes 35 seconds on
> this server!
> and we defrag ALL the indexes after every load.
> thanks for your ideas if you have one :-)
> Jerome.
>
|||Hi,
yes, I have played with the mdop option.
sometimes this improve the performance, sometimes this decrease the
performance.
for queries where I have "simple" joins and group by clause, I have less
issues then queries with outer join and lookup queires (queries statement
under the select statement select (select min(...) from tableA... ) as
mindate from tableB...))
The overall result of the entire process is a slower result when maxdop = 1.
update statements are always slow, and if I update a column with an index on
it... its really bad!
I have tried to add some files in my filegroups, but the result is not so
good, my staging queries generate 8 times more CXPacket locks.
I'll try some options and test it again.
if you have some other ideas, I'll take it ;-)
"Danny" <someone@.nowhere.com> wrote in message
news:91Gef.7467$vS4.4914@.trnddc01...
> Jeje,
> If the Average Queue Length is high then there is a disk bottle neck. The
> disk subsystem may be optimal but still not fast enough for what you need.
> Moving to more than one file in a filegroup on a multiprocessor server is
> generally helpful. You've effectively increased the number of parrallel
> tasks. Have you tried lowering your mdop?
>
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:%23PsrShk6FHA.3888@.TK2MSFTNGP10.phx.gbl...
>
Thursday, March 8, 2012
awe question
We are going to turn on AWE on our production environment currently
its 4 processor box with 12GB of RAM and windows advanced server 2000
loaded on it. Now my question is do i need to use /PAE and /3GB
switches in our boot.ini in order to enable 6gigs to our sql server
or /PAE will okay?kulkarni.ninad@.gmail.com wrote:
> We are going to turn on AWE on our production environment currently
> its 4 processor box with 12GB of RAM and windows advanced server 2000
> loaded on it. Now my question is do i need to use /PAE and /3GB
> switches in our boot.ini in order to enable 6gigs to our sql server
> or /PAE will okay?
>
With 6Gb of RAM, I would use the /3GB switch as well. That would
effectively allow SQL to use as much as 5GB. Without the /3Gb switch
you are pretty well limited to 4Gb.|||That is not really true. /3GB allows SQL Server (or any properly enabled
application) to use up to 3GB of the 4GB of directly addressable memory but
limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
the App to see memory above 4GB and is totally independent of the /3GB
switch. The decision to use /3GB or not is dependent on how much of that
directly addressable memory you need to use. The only part of SQL Server
that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
cache. I would question why he wants to use only 6GB when he has 12
available. I suspect he may be in a cluster and if that is true and it is
Active-Active then 6GB may be too much since AWE memory is fixed and can not
be dynamically adjusted.
Andrew J. Kelly SQL MVP
"Don Peterson" <sgtp.usmc@.gmail.com> wrote in message
news:uEmOq6LbHHA.5044@.TK2MSFTNGP05.phx.gbl...
> kulkarni.ninad@.gmail.com wrote:
> With 6Gb of RAM, I would use the /3GB switch as well. That would
> effectively allow SQL to use as much as 5GB. Without the /3Gb switch you
> are pretty well limited to 4Gb.|||Andrew J. Kelly wrote:
> That is not really true. /3GB allows SQL Server (or any properly enabled
> application) to use up to 3GB of the 4GB of directly addressable memory bu
t
> limits the OS to 1GB of that same space. AWE and PAE are to allow the OS a
nd
> the App to see memory above 4GB and is totally independent of the /3GB
> switch. The decision to use /3GB or not is dependent on how much of that
> directly addressable memory you need to use. The only part of SQL Server
> that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
> cache. I would question why he wants to use only 6GB when he has 12
> available. I suspect he may be in a cluster and if that is true and it is
> Active-Active then 6GB may be too much since AWE memory is fixed and can n
ot
> be dynamically adjusted.
>
My thinking was that there was only 6Gb and if you didn't use the /3Gb
switch, then the OS would effectively take the full 2Gb available to it,
leaving 4Gb for applications such as SQL. While using the /3Gb switch
would limit the OS to 1Gb, thereby allowing SQL to use a full 5Gb.
However, now I see that there is actually 12Gb...
With 12Gb you don't need the /3Gb switch in order to allow SQL to see
6Gb (actually it could use 10Gb), however, I have to wonder why you want
to limit SQL to 6Gb when you have 12?
I think my recommendation still stands though, because if I recall, it's
not until you have over 16Gb of RAM that the OS has to use that extra Gb
of space to maintain the Address Extensions.
So, using the /3Gb switch would allow SQL to use up to 11Gb instead of
10Gb without it. Although I'd keep a close eye on things to make sure
that the OS is not having to do a bunch of swapping. In certain
circumstances the OS might be able to make better use of that Gb than SQL.|||> I think my recommendation still stands though, because if I recall, it's
> not until you have over 16Gb of RAM that the OS has to use that extra Gb
> of space to maintain the Address Extensions.
I believe that Unisys has recommendations to not use /3GB if you have > 12GB
in the machine.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don Peterson" <sgtp.usmc@.gmail.com> wrote in message news:uhqohQYbHHA.588@.TK2MSFTNGP06.phx.
gbl...
> Andrew J. Kelly wrote:
> My thinking was that there was only 6Gb and if you didn't use the /3Gb
> switch, then the OS would effectively take the full 2Gb available to it,
> leaving 4Gb for applications such as SQL. While using the /3Gb switch
> would limit the OS to 1Gb, thereby allowing SQL to use a full 5Gb.
> However, now I see that there is actually 12Gb...
> With 12Gb you don't need the /3Gb switch in order to allow SQL to see
> 6Gb (actually it could use 10Gb), however, I have to wonder why you want
> to limit SQL to 6Gb when you have 12?
> I think my recommendation still stands though, because if I recall, it's
> not until you have over 16Gb of RAM that the OS has to use that extra Gb
> of space to maintain the Address Extensions.
> So, using the /3Gb switch would allow SQL to use up to 11Gb instead of
> 10Gb without it. Although I'd keep a close eye on things to make sure
> that the OS is not having to do a bunch of swapping. In certain
> circumstances the OS might be able to make better use of that Gb than SQL.
its 4 processor box with 12GB of RAM and windows advanced server 2000
loaded on it. Now my question is do i need to use /PAE and /3GB
switches in our boot.ini in order to enable 6gigs to our sql server
or /PAE will okay?kulkarni.ninad@.gmail.com wrote:
> We are going to turn on AWE on our production environment currently
> its 4 processor box with 12GB of RAM and windows advanced server 2000
> loaded on it. Now my question is do i need to use /PAE and /3GB
> switches in our boot.ini in order to enable 6gigs to our sql server
> or /PAE will okay?
>
With 6Gb of RAM, I would use the /3GB switch as well. That would
effectively allow SQL to use as much as 5GB. Without the /3Gb switch
you are pretty well limited to 4Gb.|||That is not really true. /3GB allows SQL Server (or any properly enabled
application) to use up to 3GB of the 4GB of directly addressable memory but
limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
the App to see memory above 4GB and is totally independent of the /3GB
switch. The decision to use /3GB or not is dependent on how much of that
directly addressable memory you need to use. The only part of SQL Server
that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
cache. I would question why he wants to use only 6GB when he has 12
available. I suspect he may be in a cluster and if that is true and it is
Active-Active then 6GB may be too much since AWE memory is fixed and can not
be dynamically adjusted.
Andrew J. Kelly SQL MVP
"Don Peterson" <sgtp.usmc@.gmail.com> wrote in message
news:uEmOq6LbHHA.5044@.TK2MSFTNGP05.phx.gbl...
> kulkarni.ninad@.gmail.com wrote:
> With 6Gb of RAM, I would use the /3GB switch as well. That would
> effectively allow SQL to use as much as 5GB. Without the /3Gb switch you
> are pretty well limited to 4Gb.|||Andrew J. Kelly wrote:
> That is not really true. /3GB allows SQL Server (or any properly enabled
> application) to use up to 3GB of the 4GB of directly addressable memory bu
t
> limits the OS to 1GB of that same space. AWE and PAE are to allow the OS a
nd
> the App to see memory above 4GB and is totally independent of the /3GB
> switch. The decision to use /3GB or not is dependent on how much of that
> directly addressable memory you need to use. The only part of SQL Server
> that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
> cache. I would question why he wants to use only 6GB when he has 12
> available. I suspect he may be in a cluster and if that is true and it is
> Active-Active then 6GB may be too much since AWE memory is fixed and can n
ot
> be dynamically adjusted.
>
My thinking was that there was only 6Gb and if you didn't use the /3Gb
switch, then the OS would effectively take the full 2Gb available to it,
leaving 4Gb for applications such as SQL. While using the /3Gb switch
would limit the OS to 1Gb, thereby allowing SQL to use a full 5Gb.
However, now I see that there is actually 12Gb...
With 12Gb you don't need the /3Gb switch in order to allow SQL to see
6Gb (actually it could use 10Gb), however, I have to wonder why you want
to limit SQL to 6Gb when you have 12?
I think my recommendation still stands though, because if I recall, it's
not until you have over 16Gb of RAM that the OS has to use that extra Gb
of space to maintain the Address Extensions.
So, using the /3Gb switch would allow SQL to use up to 11Gb instead of
10Gb without it. Although I'd keep a close eye on things to make sure
that the OS is not having to do a bunch of swapping. In certain
circumstances the OS might be able to make better use of that Gb than SQL.|||> I think my recommendation still stands though, because if I recall, it's
> not until you have over 16Gb of RAM that the OS has to use that extra Gb
> of space to maintain the Address Extensions.
I believe that Unisys has recommendations to not use /3GB if you have > 12GB
in the machine.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don Peterson" <sgtp.usmc@.gmail.com> wrote in message news:uhqohQYbHHA.588@.TK2MSFTNGP06.phx.
gbl...
> Andrew J. Kelly wrote:
> My thinking was that there was only 6Gb and if you didn't use the /3Gb
> switch, then the OS would effectively take the full 2Gb available to it,
> leaving 4Gb for applications such as SQL. While using the /3Gb switch
> would limit the OS to 1Gb, thereby allowing SQL to use a full 5Gb.
> However, now I see that there is actually 12Gb...
> With 12Gb you don't need the /3Gb switch in order to allow SQL to see
> 6Gb (actually it could use 10Gb), however, I have to wonder why you want
> to limit SQL to 6Gb when you have 12?
> I think my recommendation still stands though, because if I recall, it's
> not until you have over 16Gb of RAM that the OS has to use that extra Gb
> of space to maintain the Address Extensions.
> So, using the /3Gb switch would allow SQL to use up to 11Gb instead of
> 10Gb without it. Although I'd keep a close eye on things to make sure
> that the OS is not having to do a bunch of swapping. In certain
> circumstances the OS might be able to make better use of that Gb than SQL.
awe question
We are going to turn on AWE on our production environment currently
its 4 processor box with 12GB of RAM and windows advanced server 2000
loaded on it. Now my question is do i need to use /PAE and /3GB
switches in our boot.ini in order to enable 6gigs to our sql server
or /PAE will okay?
kulkarni.ninad@.gmail.com wrote:
> We are going to turn on AWE on our production environment currently
> its 4 processor box with 12GB of RAM and windows advanced server 2000
> loaded on it. Now my question is do i need to use /PAE and /3GB
> switches in our boot.ini in order to enable 6gigs to our sql server
> or /PAE will okay?
>
With 6Gb of RAM, I would use the /3GB switch as well. That would
effectively allow SQL to use as much as 5GB. Without the /3Gb switch
you are pretty well limited to 4Gb.
|||That is not really true. /3GB allows SQL Server (or any properly enabled
application) to use up to 3GB of the 4GB of directly addressable memory but
limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
the App to see memory above 4GB and is totally independent of the /3GB
switch. The decision to use /3GB or not is dependent on how much of that
directly addressable memory you need to use. The only part of SQL Server
that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
cache. I would question why he wants to use only 6GB when he has 12
available. I suspect he may be in a cluster and if that is true and it is
Active-Active then 6GB may be too much since AWE memory is fixed and can not
be dynamically adjusted.
Andrew J. Kelly SQL MVP
"Don Peterson" <sgtp.usmc@.gmail.com> wrote in message
news:uEmOq6LbHHA.5044@.TK2MSFTNGP05.phx.gbl...
> kulkarni.ninad@.gmail.com wrote:
> With 6Gb of RAM, I would use the /3GB switch as well. That would
> effectively allow SQL to use as much as 5GB. Without the /3Gb switch you
> are pretty well limited to 4Gb.
|||Andrew J. Kelly wrote:
> That is not really true. /3GB allows SQL Server (or any properly enabled
> application) to use up to 3GB of the 4GB of directly addressable memory but
> limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
> the App to see memory above 4GB and is totally independent of the /3GB
> switch. The decision to use /3GB or not is dependent on how much of that
> directly addressable memory you need to use. The only part of SQL Server
> that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
> cache. I would question why he wants to use only 6GB when he has 12
> available. I suspect he may be in a cluster and if that is true and it is
> Active-Active then 6GB may be too much since AWE memory is fixed and can not
> be dynamically adjusted.
>
My thinking was that there was only 6Gb and if you didn't use the /3Gb
switch, then the OS would effectively take the full 2Gb available to it,
leaving 4Gb for applications such as SQL. While using the /3Gb switch
would limit the OS to 1Gb, thereby allowing SQL to use a full 5Gb.
However, now I see that there is actually 12Gb...
With 12Gb you don't need the /3Gb switch in order to allow SQL to see
6Gb (actually it could use 10Gb), however, I have to wonder why you want
to limit SQL to 6Gb when you have 12?
I think my recommendation still stands though, because if I recall, it's
not until you have over 16Gb of RAM that the OS has to use that extra Gb
of space to maintain the Address Extensions.
So, using the /3Gb switch would allow SQL to use up to 11Gb instead of
10Gb without it. Although I'd keep a close eye on things to make sure
that the OS is not having to do a bunch of swapping. In certain
circumstances the OS might be able to make better use of that Gb than SQL.
its 4 processor box with 12GB of RAM and windows advanced server 2000
loaded on it. Now my question is do i need to use /PAE and /3GB
switches in our boot.ini in order to enable 6gigs to our sql server
or /PAE will okay?
kulkarni.ninad@.gmail.com wrote:
> We are going to turn on AWE on our production environment currently
> its 4 processor box with 12GB of RAM and windows advanced server 2000
> loaded on it. Now my question is do i need to use /PAE and /3GB
> switches in our boot.ini in order to enable 6gigs to our sql server
> or /PAE will okay?
>
With 6Gb of RAM, I would use the /3GB switch as well. That would
effectively allow SQL to use as much as 5GB. Without the /3Gb switch
you are pretty well limited to 4Gb.
|||That is not really true. /3GB allows SQL Server (or any properly enabled
application) to use up to 3GB of the 4GB of directly addressable memory but
limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
the App to see memory above 4GB and is totally independent of the /3GB
switch. The decision to use /3GB or not is dependent on how much of that
directly addressable memory you need to use. The only part of SQL Server
that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
cache. I would question why he wants to use only 6GB when he has 12
available. I suspect he may be in a cluster and if that is true and it is
Active-Active then 6GB may be too much since AWE memory is fixed and can not
be dynamically adjusted.
Andrew J. Kelly SQL MVP
"Don Peterson" <sgtp.usmc@.gmail.com> wrote in message
news:uEmOq6LbHHA.5044@.TK2MSFTNGP05.phx.gbl...
> kulkarni.ninad@.gmail.com wrote:
> With 6Gb of RAM, I would use the /3GB switch as well. That would
> effectively allow SQL to use as much as 5GB. Without the /3Gb switch you
> are pretty well limited to 4Gb.
|||Andrew J. Kelly wrote:
> That is not really true. /3GB allows SQL Server (or any properly enabled
> application) to use up to 3GB of the 4GB of directly addressable memory but
> limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
> the App to see memory above 4GB and is totally independent of the /3GB
> switch. The decision to use /3GB or not is dependent on how much of that
> directly addressable memory you need to use. The only part of SQL Server
> that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
> cache. I would question why he wants to use only 6GB when he has 12
> available. I suspect he may be in a cluster and if that is true and it is
> Active-Active then 6GB may be too much since AWE memory is fixed and can not
> be dynamically adjusted.
>
My thinking was that there was only 6Gb and if you didn't use the /3Gb
switch, then the OS would effectively take the full 2Gb available to it,
leaving 4Gb for applications such as SQL. While using the /3Gb switch
would limit the OS to 1Gb, thereby allowing SQL to use a full 5Gb.
However, now I see that there is actually 12Gb...
With 12Gb you don't need the /3Gb switch in order to allow SQL to see
6Gb (actually it could use 10Gb), however, I have to wonder why you want
to limit SQL to 6Gb when you have 12?
I think my recommendation still stands though, because if I recall, it's
not until you have over 16Gb of RAM that the OS has to use that extra Gb
of space to maintain the Address Extensions.
So, using the /3Gb switch would allow SQL to use up to 11Gb instead of
10Gb without it. Although I'd keep a close eye on things to make sure
that the OS is not having to do a bunch of swapping. In certain
circumstances the OS might be able to make better use of that Gb than SQL.
awe question
We are going to turn on AWE on our production environment currently
its 4 processor box with 12GB of RAM and windows advanced server 2000
loaded on it. Now my question is do i need to use /PAE and /3GB
switches in our boot.ini in order to enable 6gigs to our sql server
or /PAE will okay?kulkarni.ninad@.gmail.com wrote:
> We are going to turn on AWE on our production environment currently
> its 4 processor box with 12GB of RAM and windows advanced server 2000
> loaded on it. Now my question is do i need to use /PAE and /3GB
> switches in our boot.ini in order to enable 6gigs to our sql server
> or /PAE will okay?
>
With 6Gb of RAM, I would use the /3GB switch as well. That would
effectively allow SQL to use as much as 5GB. Without the /3Gb switch
you are pretty well limited to 4Gb.|||That is not really true. /3GB allows SQL Server (or any properly enabled
application) to use up to 3GB of the 4GB of directly addressable memory but
limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
the App to see memory above 4GB and is totally independent of the /3GB
switch. The decision to use /3GB or not is dependent on how much of that
directly addressable memory you need to use. The only part of SQL Server
that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
cache. I would question why he wants to use only 6GB when he has 12
available. I suspect he may be in a cluster and if that is true and it is
Active-Active then 6GB may be too much since AWE memory is fixed and can not
be dynamically adjusted.
--
Andrew J. Kelly SQL MVP
"Don Peterson" <sgtp.usmc@.gmail.com> wrote in message
news:uEmOq6LbHHA.5044@.TK2MSFTNGP05.phx.gbl...
> kulkarni.ninad@.gmail.com wrote:
>> We are going to turn on AWE on our production environment currently
>> its 4 processor box with 12GB of RAM and windows advanced server 2000
>> loaded on it. Now my question is do i need to use /PAE and /3GB
>> switches in our boot.ini in order to enable 6gigs to our sql server
>> or /PAE will okay?
> With 6Gb of RAM, I would use the /3GB switch as well. That would
> effectively allow SQL to use as much as 5GB. Without the /3Gb switch you
> are pretty well limited to 4Gb.|||Andrew J. Kelly wrote:
> That is not really true. /3GB allows SQL Server (or any properly enabled
> application) to use up to 3GB of the 4GB of directly addressable memory but
> limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
> the App to see memory above 4GB and is totally independent of the /3GB
> switch. The decision to use /3GB or not is dependent on how much of that
> directly addressable memory you need to use. The only part of SQL Server
> that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
> cache. I would question why he wants to use only 6GB when he has 12
> available. I suspect he may be in a cluster and if that is true and it is
> Active-Active then 6GB may be too much since AWE memory is fixed and can not
> be dynamically adjusted.
>
My thinking was that there was only 6Gb and if you didn't use the /3Gb
switch, then the OS would effectively take the full 2Gb available to it,
leaving 4Gb for applications such as SQL. While using the /3Gb switch
would limit the OS to 1Gb, thereby allowing SQL to use a full 5Gb.
However, now I see that there is actually 12Gb...
With 12Gb you don't need the /3Gb switch in order to allow SQL to see
6Gb (actually it could use 10Gb), however, I have to wonder why you want
to limit SQL to 6Gb when you have 12?
I think my recommendation still stands though, because if I recall, it's
not until you have over 16Gb of RAM that the OS has to use that extra Gb
of space to maintain the Address Extensions.
So, using the /3Gb switch would allow SQL to use up to 11Gb instead of
10Gb without it. Although I'd keep a close eye on things to make sure
that the OS is not having to do a bunch of swapping. In certain
circumstances the OS might be able to make better use of that Gb than SQL.|||> I think my recommendation still stands though, because if I recall, it's
> not until you have over 16Gb of RAM that the OS has to use that extra Gb
> of space to maintain the Address Extensions.
I believe that Unisys has recommendations to not use /3GB if you have > 12GB in the machine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don Peterson" <sgtp.usmc@.gmail.com> wrote in message news:uhqohQYbHHA.588@.TK2MSFTNGP06.phx.gbl...
> Andrew J. Kelly wrote:
>> That is not really true. /3GB allows SQL Server (or any properly enabled
>> application) to use up to 3GB of the 4GB of directly addressable memory but
>> limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
>> the App to see memory above 4GB and is totally independent of the /3GB
>> switch. The decision to use /3GB or not is dependent on how much of that
>> directly addressable memory you need to use. The only part of SQL Server
>> that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
>> cache. I would question why he wants to use only 6GB when he has 12
>> available. I suspect he may be in a cluster and if that is true and it is
>> Active-Active then 6GB may be too much since AWE memory is fixed and can not
>> be dynamically adjusted.
> My thinking was that there was only 6Gb and if you didn't use the /3Gb
> switch, then the OS would effectively take the full 2Gb available to it,
> leaving 4Gb for applications such as SQL. While using the /3Gb switch
> would limit the OS to 1Gb, thereby allowing SQL to use a full 5Gb.
> However, now I see that there is actually 12Gb...
> With 12Gb you don't need the /3Gb switch in order to allow SQL to see
> 6Gb (actually it could use 10Gb), however, I have to wonder why you want
> to limit SQL to 6Gb when you have 12?
> I think my recommendation still stands though, because if I recall, it's
> not until you have over 16Gb of RAM that the OS has to use that extra Gb
> of space to maintain the Address Extensions.
> So, using the /3Gb switch would allow SQL to use up to 11Gb instead of
> 10Gb without it. Although I'd keep a close eye on things to make sure
> that the OS is not having to do a bunch of swapping. In certain
> circumstances the OS might be able to make better use of that Gb than SQL.
its 4 processor box with 12GB of RAM and windows advanced server 2000
loaded on it. Now my question is do i need to use /PAE and /3GB
switches in our boot.ini in order to enable 6gigs to our sql server
or /PAE will okay?kulkarni.ninad@.gmail.com wrote:
> We are going to turn on AWE on our production environment currently
> its 4 processor box with 12GB of RAM and windows advanced server 2000
> loaded on it. Now my question is do i need to use /PAE and /3GB
> switches in our boot.ini in order to enable 6gigs to our sql server
> or /PAE will okay?
>
With 6Gb of RAM, I would use the /3GB switch as well. That would
effectively allow SQL to use as much as 5GB. Without the /3Gb switch
you are pretty well limited to 4Gb.|||That is not really true. /3GB allows SQL Server (or any properly enabled
application) to use up to 3GB of the 4GB of directly addressable memory but
limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
the App to see memory above 4GB and is totally independent of the /3GB
switch. The decision to use /3GB or not is dependent on how much of that
directly addressable memory you need to use. The only part of SQL Server
that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
cache. I would question why he wants to use only 6GB when he has 12
available. I suspect he may be in a cluster and if that is true and it is
Active-Active then 6GB may be too much since AWE memory is fixed and can not
be dynamically adjusted.
--
Andrew J. Kelly SQL MVP
"Don Peterson" <sgtp.usmc@.gmail.com> wrote in message
news:uEmOq6LbHHA.5044@.TK2MSFTNGP05.phx.gbl...
> kulkarni.ninad@.gmail.com wrote:
>> We are going to turn on AWE on our production environment currently
>> its 4 processor box with 12GB of RAM and windows advanced server 2000
>> loaded on it. Now my question is do i need to use /PAE and /3GB
>> switches in our boot.ini in order to enable 6gigs to our sql server
>> or /PAE will okay?
> With 6Gb of RAM, I would use the /3GB switch as well. That would
> effectively allow SQL to use as much as 5GB. Without the /3Gb switch you
> are pretty well limited to 4Gb.|||Andrew J. Kelly wrote:
> That is not really true. /3GB allows SQL Server (or any properly enabled
> application) to use up to 3GB of the 4GB of directly addressable memory but
> limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
> the App to see memory above 4GB and is totally independent of the /3GB
> switch. The decision to use /3GB or not is dependent on how much of that
> directly addressable memory you need to use. The only part of SQL Server
> that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
> cache. I would question why he wants to use only 6GB when he has 12
> available. I suspect he may be in a cluster and if that is true and it is
> Active-Active then 6GB may be too much since AWE memory is fixed and can not
> be dynamically adjusted.
>
My thinking was that there was only 6Gb and if you didn't use the /3Gb
switch, then the OS would effectively take the full 2Gb available to it,
leaving 4Gb for applications such as SQL. While using the /3Gb switch
would limit the OS to 1Gb, thereby allowing SQL to use a full 5Gb.
However, now I see that there is actually 12Gb...
With 12Gb you don't need the /3Gb switch in order to allow SQL to see
6Gb (actually it could use 10Gb), however, I have to wonder why you want
to limit SQL to 6Gb when you have 12?
I think my recommendation still stands though, because if I recall, it's
not until you have over 16Gb of RAM that the OS has to use that extra Gb
of space to maintain the Address Extensions.
So, using the /3Gb switch would allow SQL to use up to 11Gb instead of
10Gb without it. Although I'd keep a close eye on things to make sure
that the OS is not having to do a bunch of swapping. In certain
circumstances the OS might be able to make better use of that Gb than SQL.|||> I think my recommendation still stands though, because if I recall, it's
> not until you have over 16Gb of RAM that the OS has to use that extra Gb
> of space to maintain the Address Extensions.
I believe that Unisys has recommendations to not use /3GB if you have > 12GB in the machine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don Peterson" <sgtp.usmc@.gmail.com> wrote in message news:uhqohQYbHHA.588@.TK2MSFTNGP06.phx.gbl...
> Andrew J. Kelly wrote:
>> That is not really true. /3GB allows SQL Server (or any properly enabled
>> application) to use up to 3GB of the 4GB of directly addressable memory but
>> limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
>> the App to see memory above 4GB and is totally independent of the /3GB
>> switch. The decision to use /3GB or not is dependent on how much of that
>> directly addressable memory you need to use. The only part of SQL Server
>> that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
>> cache. I would question why he wants to use only 6GB when he has 12
>> available. I suspect he may be in a cluster and if that is true and it is
>> Active-Active then 6GB may be too much since AWE memory is fixed and can not
>> be dynamically adjusted.
> My thinking was that there was only 6Gb and if you didn't use the /3Gb
> switch, then the OS would effectively take the full 2Gb available to it,
> leaving 4Gb for applications such as SQL. While using the /3Gb switch
> would limit the OS to 1Gb, thereby allowing SQL to use a full 5Gb.
> However, now I see that there is actually 12Gb...
> With 12Gb you don't need the /3Gb switch in order to allow SQL to see
> 6Gb (actually it could use 10Gb), however, I have to wonder why you want
> to limit SQL to 6Gb when you have 12?
> I think my recommendation still stands though, because if I recall, it's
> not until you have over 16Gb of RAM that the OS has to use that extra Gb
> of space to maintain the Address Extensions.
> So, using the /3Gb switch would allow SQL to use up to 11Gb instead of
> 10Gb without it. Although I'd keep a close eye on things to make sure
> that the OS is not having to do a bunch of swapping. In certain
> circumstances the OS might be able to make better use of that Gb than SQL.
Subscribe to:
Posts (Atom)