Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

A Guide to Application Memory Tuning Expand / Collapse
Author
Message
Posted Thursday, October 12, 2006 6:49 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 10:02 AM
Points: 339, Visits: 390
Comments posted here are about the content posted at temp
Post #315143
Posted Monday, October 16, 2006 1:09 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 27, 2010 3:05 PM
Points: 1,629, 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.



Post #315538
Posted Monday, October 16, 2006 2:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 9:12 AM
Points: 100, Visits: 138
A bit more detail on how all this effects 64bit machines (memory page locking etc) I think would of been useful
Post #315542
Posted Monday, October 16, 2006 6:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 4, 2012 11:13 AM
Points: 257, 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?

Post #315575
Posted Monday, October 16, 2006 6:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 10:02 AM
Points: 339, Visits: 390

"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.


 

Post #315580
Posted Monday, October 16, 2006 1:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 7, 2010 2:39 PM
Points: 30, 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? 




Post #315702
Posted Monday, October 16, 2006 5:59 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 10:02 AM
Points: 339, Visits: 390

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.

Post #315748
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse