SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Guide to Application Memory Tuning


A Guide to Application Memory Tuning

Author
Message
Paul Mu
Paul Mu
Mr or Mrs. 500
Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)

Group: General Forum Members
Points: 583 Visits: 391
Comments posted here are about the content posted at temp
gbn
gbn
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1751 Visits: 62
"For a dedicated database server, the default memory settings for SQL Server should suffice."

Not sure I agree with this (the context is having AWE enabled)
This means a SQL 2000 instance with AWE enabled will leave only 128MB for the OS.
Having seen this happen, I can say with confidence that the OS will start paging like mad and cause some frantic phone calls from the WIndows team...

Always set min and max memory with AWE enabled on SQL 2000.



A Joy
A Joy
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 138
A bit more detail on how all this effects 64bit machines (memory page locking etc) I think would of been useful
Brian Munier
Brian Munier
Old Hand
Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)

Group: General Forum Members
Points: 371 Visits: 80

I agree with the comment of not leaving defaults, as I have seen that that usually means take all be 1 MB of memory.

I also believe that if you go above 2 or 4 GB, dynamic memory utilization is no longer possible, SQL Server has to go into fixed memory size. It may not start out with that much memory, but it will not give it back. I have also seen Enterprise manager refuse to switch to dynamic memory when there was that much available.

The first part about the OS and memory was good.

One question, does this mean I should always leave at least 1GB physical Ram for the os?


Paul Mu
Paul Mu
Mr or Mrs. 500
Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)

Group: General Forum Members
Points: 583 Visits: 391

"This means a SQL 2000 instance with AWE enabled will leave only 128MB for the OS."

I always set the maximum memory to allow for the OS to have at least 1GB. If I remember correctly, the default max configured memory for AWE on SQL Server appears to be much more than what you've suggested.

As to coverage on 64bit machines, maybe someone with some hands on experience can write an article on this topic.



NotElite
NotElite
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 44

This is actually one of those topics I've seen discussed and rediscussed extensively, yielding more confusion than answers mostly.

What I want to know is if I set the /3gb switch on a 4GB server, is that all I need to do? From what I've read, this alone will not give all 3GB to SQL Server because the 32 bit architecture will still not address space beyond 2GB to any one application.

If this is the case, do we need to configure /3gb and PAE with AWE even on a 4GB machine?





Paul Mu
Paul Mu
Mr or Mrs. 500
Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)Mr or Mrs. 500 (583 reputation)

Group: General Forum Members
Points: 583 Visits: 391

To answer your particular question, for a 4GB production server, you are limited to using the /3GB switch. The /PAE switch is only applicable to OSs having more than 4GB of RAM. For this reason, you will most likely be using a Standard Edition of SQL Server, which does not support AWE.

A number of things to keep in mind:

Firstly, the article starts by discussing the two switches, which is related to the OS. Use the hyperlinks to check that your particular operating system support the two switches.

Secondly, determine if your particular version of SQL Server supports AWE. Again use the hyperlinks to check.

Thirdly, determine if you wish to configure (i) BOTH switches with AWE enabled for SQL Server for systems with between 4 and 16 GB of RAM, (ii) just the /3GB switch for systems with less than 4GB RAM or (iii) just the /PAE switch with AWE enabled on SQL Server for systems with more than 4GB RAM.

Lastly, set the max memory setting for SQL Server to a value that is right for your system. This is the 'tuning' part, where you may need to change the default value when AWE is enabled on SQL Server. The default value may be appropriate for a 'dedicated' Database server. My production servers are on SS2K EE with 7-8 GB RAM, and I have the max memory setting set to around 1 GB. Whatever value you set, you may need to monitor it for a few days. HTH.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search