| Groups | Blog | Home |
sql server clustering : Memory settings for Virtual SQL Server
Hi all,
I am planning to put one instance of SQL Server 2000 sp3 on a two node Windows 2003 EE MSCS. Each node has two channels, with each channel running RAID 1. The page file is in one channel, and the OS in the other. Each node has 4GB of RAM, and PAE enabled. The pagefiles are locked at 8GB on each server. MDB's and LOG's will run on separate LUN's in the shared storage array, with MDB's configured as RAID 1/0 and LOGs as RAID 1. Both nodes will be dedicated to SQL Server. 1. Should I enable AWE in this situation? 2. Should I use the /3GB switch? 3. Would you put the SQL bits in the OS RAID group or the pagefile RAID group? 4. What settings for sp_configure 'min server memory'? 5. For sp_configure 'max server memory'? 6. Settings for Sp_configure 'max worker thread'? 7. Use WindowsNT fibers? 8. Settings for set working set size? 0? Thanks for any advice. --
Thank you Geoff.
-- John [quoted text, click to view] "Geoff N. Hiten" wrote:
> 1. No. And you can remove the /PAE switch from boot.ini. Unless you have > MORE than 4GB of RAM , you don't need it. > 2. No. You will need to have flexible memory incase the two instances land > on the same host during a failover event. > 3. Forget the pagefile RAID group. Put the pagefile on the OS partition. > A properly tuned SQL server should not page. Put SQL Data and Logs on > different RAID groups. If you only have two RAID groups, put Data on its > own group and Logs on with the OS bits. > 4. No change. > 5. No change. With 4GB of RAM , it is best to allow SQL to determine its > own memory usage. If you end up with two instances on the same node, they > will come to a balance. You can lock those numbers into the settings, but > be aware that changes in load man affect memory demand between the two > instances. > 6. Unless PSS tells you, don't change it. > 7. No. Very No. > 8. Again, go with the default. > > Geoff N. Hiten > Microsoft SQL Server MVP > > "JT" <Jthayer@online.nospam> wrote in message > news:371EAB65-6324-4ABC-A798-E0A114B528B2@microsoft.com... > > Hi all, > > I am planning to put one instance of SQL Server 2000 sp3 on a two node > > Windows 2003 EE MSCS. Each node has two channels, with each channel > > running > > RAID 1. The page file is in one channel, and the OS in the other. Each > > node > > has 4GB of RAM, and PAE enabled. The pagefiles are locked at 8GB on each > > server. MDB's and LOG's will run on separate LUN's in the shared storage > > array, with MDB's configured as RAID 1/0 and LOGs as RAID 1. Both nodes > > will > > be dedicated to SQL Server. > > > > 1. Should I enable AWE in this situation? > > 2. Should I use the /3GB switch? > > 3. Would you put the SQL bits in the OS RAID group or the pagefile RAID > > group? > > 4. What settings for sp_configure 'min server memory'? > > 5. For sp_configure 'max server memory'? > > 6. Settings for Sp_configure 'max worker thread'? > > 7. Use WindowsNT fibers? > > 8. Settings for set working set size? 0? > > > > Thanks for any advice. > > > > -- > > John > >
1. No. And you can remove the /PAE switch from boot.ini. Unless you have
MORE than 4GB of RAM , you don't need it. 2. No. You will need to have flexible memory incase the two instances land on the same host during a failover event. 3. Forget the pagefile RAID group. Put the pagefile on the OS partition. A properly tuned SQL server should not page. Put SQL Data and Logs on different RAID groups. If you only have two RAID groups, put Data on its own group and Logs on with the OS bits. 4. No change. 5. No change. With 4GB of RAM , it is best to allow SQL to determine its own memory usage. If you end up with two instances on the same node, they will come to a balance. You can lock those numbers into the settings, but be aware that changes in load man affect memory demand between the two instances. 6. Unless PSS tells you, don't change it. 7. No. Very No. 8. Again, go with the default. Geoff N. Hiten Microsoft SQL Server MVP [quoted text, click to view] "JT" <Jthayer@online.nospam> wrote in message news:371EAB65-6324-4ABC-A798-E0A114B528B2@microsoft.com... > Hi all, > I am planning to put one instance of SQL Server 2000 sp3 on a two node > Windows 2003 EE MSCS. Each node has two channels, with each channel > running > RAID 1. The page file is in one channel, and the OS in the other. Each > node > has 4GB of RAM, and PAE enabled. The pagefiles are locked at 8GB on each > server. MDB's and LOG's will run on separate LUN's in the shared storage > array, with MDB's configured as RAID 1/0 and LOGs as RAID 1. Both nodes > will > be dedicated to SQL Server. > > 1. Should I enable AWE in this situation? > 2. Should I use the /3GB switch? > 3. Would you put the SQL bits in the OS RAID group or the pagefile RAID > group? > 4. What settings for sp_configure 'min server memory'? > 5. For sp_configure 'max server memory'? > 6. Settings for Sp_configure 'max worker thread'? > 7. Use WindowsNT fibers? > 8. Settings for set working set size? 0? > > Thanks for any advice. > > -- > John
Hi again Geoff. As I think about your answer, I think you may have
misunderstood my situation. I am not putting on two instances of SQL Server. I am putting on one instance onto a virtual server running in active/passive mode. In this situation, I would expect to not need spare ram to absorb the second instance, as there will not be one. Would your answers change, especially the /3GB, AWE and server memory options? Thanks for your additional consideration of this question. -- John [quoted text, click to view] "Geoff N. Hiten" wrote:
> 1. No. And you can remove the /PAE switch from boot.ini. Unless you have > MORE than 4GB of RAM , you don't need it. > 2. No. You will need to have flexible memory incase the two instances land > on the same host during a failover event. > 3. Forget the pagefile RAID group. Put the pagefile on the OS partition. > A properly tuned SQL server should not page. Put SQL Data and Logs on > different RAID groups. If you only have two RAID groups, put Data on its > own group and Logs on with the OS bits. > 4. No change. > 5. No change. With 4GB of RAM , it is best to allow SQL to determine its > own memory usage. If you end up with two instances on the same node, they > will come to a balance. You can lock those numbers into the settings, but > be aware that changes in load man affect memory demand between the two > instances. > 6. Unless PSS tells you, don't change it. > 7. No. Very No. > 8. Again, go with the default. > > Geoff N. Hiten > Microsoft SQL Server MVP > > "JT" <Jthayer@online.nospam> wrote in message > news:371EAB65-6324-4ABC-A798-E0A114B528B2@microsoft.com... > > Hi all, > > I am planning to put one instance of SQL Server 2000 sp3 on a two node > > Windows 2003 EE MSCS. Each node has two channels, with each channel > > running > > RAID 1. The page file is in one channel, and the OS in the other. Each > > node > > has 4GB of RAM, and PAE enabled. The pagefiles are locked at 8GB on each > > server. MDB's and LOG's will run on separate LUN's in the shared storage > > array, with MDB's configured as RAID 1/0 and LOGs as RAID 1. Both nodes > > will > > be dedicated to SQL Server. > > > > 1. Should I enable AWE in this situation? > > 2. Should I use the /3GB switch? > > 3. Would you put the SQL bits in the OS RAID group or the pagefile RAID > > group? > > 4. What settings for sp_configure 'min server memory'? > > 5. For sp_configure 'max server memory'? > > 6. Settings for Sp_configure 'max worker thread'? > > 7. Use WindowsNT fibers? > > 8. Settings for set working set size? 0? > > > > Thanks for any advice. > > > > -- > > John > >
Active/Passive is not the current nor the correct nomenclature. What you
are talking about is a Single-Instance cluster. With 4GB of RAM in each host node, I would use the /3GB switch but not AWE or PAE. This KB article may help (or it may confuse you beyond hope). How to configure memory for more than 2 GB in SQL Server http://support.microsoft.com/default.aspx?scid=kb;en-us;274750 Geoff N. Hiten Microsoft SQL Server MVP [quoted text, click to view] "JT" <Jthayer@online.nospam> wrote in message news:4757AAF6-2A34-4961-8BD0-83580457F6EB@microsoft.com... > Hi again Geoff. As I think about your answer, I think you may have > misunderstood my situation. I am not putting on two instances of SQL > Server. > I am putting on one instance onto a virtual server running in > active/passive > mode. In this situation, I would expect to not need spare ram to absorb > the > second instance, as there will not be one. Would your answers change, > especially the /3GB, AWE and server memory options? > Thanks for your additional consideration of this question. > -- > John > > > "Geoff N. Hiten" wrote: > >> 1. No. And you can remove the /PAE switch from boot.ini. Unless you >> have >> MORE than 4GB of RAM , you don't need it. >> 2. No. You will need to have flexible memory incase the two instances >> land >> on the same host during a failover event. >> 3. Forget the pagefile RAID group. Put the pagefile on the OS >> partition. >> A properly tuned SQL server should not page. Put SQL Data and Logs on >> different RAID groups. If you only have two RAID groups, put Data on its >> own group and Logs on with the OS bits. >> 4. No change. >> 5. No change. With 4GB of RAM , it is best to allow SQL to determine >> its >> own memory usage. If you end up with two instances on the same node, >> they >> will come to a balance. You can lock those numbers into the settings, >> but >> be aware that changes in load man affect memory demand between the two >> instances. >> 6. Unless PSS tells you, don't change it. >> 7. No. Very No. >> 8. Again, go with the default. >> >> Geoff N. Hiten >> Microsoft SQL Server MVP >> >> "JT" <Jthayer@online.nospam> wrote in message >> news:371EAB65-6324-4ABC-A798-E0A114B528B2@microsoft.com... >> > Hi all, >> > I am planning to put one instance of SQL Server 2000 sp3 on a two node >> > Windows 2003 EE MSCS. Each node has two channels, with each channel >> > running >> > RAID 1. The page file is in one channel, and the OS in the other. >> > Each >> > node >> > has 4GB of RAM, and PAE enabled. The pagefiles are locked at 8GB on >> > each >> > server. MDB's and LOG's will run on separate LUN's in the shared >> > storage >> > array, with MDB's configured as RAID 1/0 and LOGs as RAID 1. Both >> > nodes >> > will >> > be dedicated to SQL Server. >> > >> > 1. Should I enable AWE in this situation? >> > 2. Should I use the /3GB switch? >> > 3. Would you put the SQL bits in the OS RAID group or the pagefile >> > RAID >> > group? >> > 4. What settings for sp_configure 'min server memory'? >> > 5. For sp_configure 'max server memory'? >> > 6. Settings for Sp_configure 'max worker thread'? >> > 7. Use WindowsNT fibers? >> > 8. Settings for set working set size? 0? >> > >> > Thanks for any advice. >> > >> > -- >> > John >> >> >>
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |