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.
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
"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
|||Thank you Geoff.
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 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...
>
>
|||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...b;en-us;274750
Geoff N. Hiten
Microsoft SQL Server MVP
"JT" <Jthayer@.online.nospam> wrote in message
news:4757AAF6-2A34-4961-8BD0-83580457F6EB@.microsoft.com...[vbcol=seagreen]
> 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: