Memory setting right ?

[masri999]

I have a few questions about memory setting inherited

Windows 2003, SQL Server SP4, both Enterprise Edition 8 GB RAM

1. How to know how much Memory SQL can address ( SQL script is better
as there is no access to Perfmonitor or EM ) . ie what is maximum
memory SQL can use ?

2. In this server awe is set to 0 . Do I have to turn it to 1 so that
SQL can use all memory .

3. If awe is enabled, what is the minimum and maximum memory size need
to be set ?

4. Should I set max worker set also ?

If someone can provide examples of /PAE, /AWE. /3GB ,/USERVA etc , I am
grateful

Thanks

Srinivas

[Erland Sommarskog]

(ma******@gmail.com) writes:

1. How to know how much Memory SQL can address ( SQL script is better
as there is no access to Perfmonitor or EM ) . ie what is maximum
memory SQL can use ?

This is in Books Online. It's 64 GB for Enterprise Edition, if memory
serves. (Either I look it up, or you do. It's under SQL Server
Architecture, Implementation details. Again, if memory serves.

2. In this server awe is set to 0 . Do I have to turn it to 1 so that
SQL can use all memory .

Yes, I believe you have to turn AWE to use all that memory.

3. If awe is enabled, what is the minimum and maximum memory size need
to be set ?

You should stick to 0, and let SQL Server configure it, unless you have
a sepecial reason.

4. Should I set max worker set also ?

Don't know, but I have never heard of that.

If someone can provide examples of /PAE, /AWE. /3GB ,/USERVA etc , I am
grateful

Unfortunately, I never play with big servers myself. I've only been
eavesdropping on others who have been talking about them.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[masri999]

Thank you Erland . See my Comments in [Sri]

Thanks

Erland Sommarskog wrote:

(ma******@gmail.com) writes:

1. How to know how much Memory SQL can address ( SQL script is better
as there is no access to Perfmonitor or EM ) . ie what is maximum
memory SQL can use ?

This is in Books Online. It's 64 GB for Enterprise Edition, if memory
serves. (Either I look it up, or you do. It's under SQL Server
Architecture, Implementation details. Again, if memory serves.

[Sri] What I meant was that how much memory SQL Server can see. ie If I
put 8 GB on SQL Server System , whether SQL Server able to utilise all
the 8 GB installed and not wasted .

2. In this server awe is set to 0 . Do I have to turn it to 1 so that
SQL can use all memory .

Yes, I believe you have to turn AWE to use all that memory.

3. If awe is enabled, what is the minimum and maximum memory size need
to be set ?

You should stick to 0, and let SQL Server configure it, unless you have
a sepecial reason.

[Sri] My information is that , once you turn AWE , you should turn off
Dynamically Configure memory . Otherwise SQL Server will utilise all
the memory and leaving 128 MB to O/S . Am I right ?

4. Should I set max worker set also ?

Don't know, but I have never heard of that.

[Sri] Typo Error . This should have been set working set size .Should I
set it to 1 or 0 ?

If someone can provide examples of /PAE, /AWE. /3GB ,/USERVA etc , I am
grateful

Unfortunately, I never play with big servers myself. I've only been
eavesdropping on others who have been talking about them.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

(ma******@gmail.com) writes:

[Sri] My information is that , once you turn AWE , you should turn off
Dynamically Configure memory . Otherwise SQL Server will utilise all
the memory and leaving 128 MB to O/S . Am I right ?

Not entirely, but in essence. Since I don't this stuff too well, I looked in
Books Online. Check the topic "Using AWE Memory on Windows 2000" in Books
Online. There is also another configuration parameter "awe enabled" that
you need to change.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx