March 17, 2009 at 2:39 am
Hi,
I have inherited a cluster that has several instances of SQL Server 2005. THE OS is 64 bit and the problem I am having is that there is 10GB of memory and when I look in Task Manager and order by memory at the top is
sqlserver.exe 5,931,512 KB
sqlserver.exe 2,771,548 KB
sqlserver.exe 286,036 KB
Total 8,989,096 KB
Now I want to even this out a little as obviously one of the SQL server instances is throttling the others. My instincts tell me the one that is using the most memory is a database that is not key to the users and as such I would be happy to reign in its memory usage.
AWE
Now when I look on the memory tab of the properties for the instance there is an option to enable AWE. There are Minimum and Maximum settings in MB for server memory. Can I use this in any way to control the memory that is used.
I thought I could but all the articles that I find seem to lead me to believe that AWE only works for 32 bit - is this true?
Can I still uise these memory settings to control memory usage?
Do I need to restart the server or service for this to take affect.
Many thanks,
Ells
March 17, 2009 at 2:49 am
Ells,
The AWE settings and the Min & Max memory settings are seperate. You do not need to turn AWE on to be able to set these.
For more explanation on AWE in 64 bit please see the below article.
http://blogs.msdn.com/slavao/archive/2005/04/29/413425.aspx
March 17, 2009 at 2:50 am
- AWE is not needed for 64bit OS, it's only a window for 32bit OS to address more memory
- As mentioned, enable 'lock pages in memory' on 64bit OS
- If you have multiple instances, make sure you set the minimum and maximum memory for each instance. If you don't do this, instances will battle for memory under high pressure.
- You can prioritize instances by using processor and/or IO affinity
- make sure you have some free memory available for OS etc. Monitor ' Memory Paging' for this issue.
- As a general rule, my servers have a minimum of 1 GB free memory.
Wilfred
The best things in life are the simple things
March 17, 2009 at 3:19 am
Thanks for your help. Got all the info I need. Just one really lame question. I am assuming that the minimum and maximum settings are for physical memory only?
Thanks a lot.
Mark.
😀
March 17, 2009 at 4:39 am
yes, memory settings are for physical memory (otherwise you'll get an "financial" crisis on your database server too 😀 )
Wilfred
The best things in life are the simple things
February 15, 2013 at 1:33 pm
Hi.
Does this apply to artificial limits, like "standard" version server limiting you to 32 GB RAM?
It seems like if you could use AWE to get around "natural" 32 bit limitations to address more RAM, there's a chance something should let you get around artificial memory limits imposed on 64 bit "standard" version Windows Server OS.
Thank you
February 15, 2013 at 1:56 pm
Nope. To get around 'artificial' memory limitations on Windows Standard edition, upgrade to Enterprise.
p.s. New questions in a new thread in future please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2013 at 2:00 pm
K. Thanks.
I hope someone does something.
32 bit days and things like AWE proved you can have big memory outside OS limits. You don't even need the OS to manage the memory with the right program, just quit trying to block it.
Thank you
February 15, 2013 at 2:07 pm
The OS wasn't the limitation in 32-bit days. The addressable memory by a 32-bit process was.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2013 at 2:13 pm
My point exactly-ish.
Before, the hw/sw couldn't do it so it was programmed around.
Now to squeeze more money from you, just the OS restricts you to force you to upgrade.
The same genious to work around should apply in either case, I am hoping.
In either case we don't need the OS to manage the memory, technically.
Thank you for your insight.
I've been trying to figure out what was up got a bit.
February 15, 2013 at 2:18 pm
Aurelio Alvarez (2/15/2013)
Now to squeeze more money from you, just the OS restricts you to force you to upgrade.
Been that way well before the 32 bit/64 bit changes.
The same genious to work around should apply in either case, I am hoping.
So you want the OS to provide a mechanism for you to evade the OS's built in memory limits? o.O
In either case we don't need the OS to manage the memory, technically.
Except the OS did manage AWE memory... Hence why the PAE switch was required for the OS before SQL could have AWE enabled.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2013 at 2:24 pm
If its an artificial limitation yes.
I get it. You dig Microsoft.
I dig some of their stuff.
I'm not evading anything, just saying 32 bit OS never was designed for big memory yet good programming dealt with it.
Now it's a built in artificial limit to squeeze more money from you.
You and I both know they didn't squeeze more money from you to lift the 32 bit OS limits. We paid good money for SQL that rightly got around it.
Now that same SQL aids and abets squeezing even more for you though both SW and OS easily support it.
Anyway, it's not evading to expect software you bought that intelligently helped you overcome OS memory limits before to continue to do do now that its 64 bit and easily could.
I appreciate your insight regardless of difference of opinion on the non-technicals.
February 15, 2013 at 2:35 pm
A. A. (2/15/2013)
You and I both know they didn't squeeze more money from you to lift the 32 bit OS limits. We paid good money for SQL that rightly got around it.
*sigh* The facility to get around the 32 bit limits was built into the OS. Any program that could access the AWE APIs (OS-level APIs) could access memory above the 4GB boundary. Hence it was not SQL that was evading an OS limitation, it was SQL using the OS-provided APIs to access memory above what a 32-bit process can directly address
Anyway, it's not evading to expect software you bought that intelligently helped you overcome OS memory limits before to continue to do do now that its 64 bit and easily could.
Again, the 32 bit limit was not an OS limitation. The OS was what provided the ability for 32 bit processes to access memory above the 4 GB boundary.
Now, if you want to continue to believe the opposite, be my guest. This is not MS fandom, this is fact, you can go and read up on memory architecture and memory access and it'll tell you just the same.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2013 at 3:01 pm
I know what I'm talking about too, though less than you I admit.
I'm talking about the general attitude of the OS, and I think we're diverging on politics.
Obviously 32 bit OS couldn't "handle" more than that or it would have, and there would not have been a limit. Instead, it let you enable relinquishing control to special programs designed to really handle it.
Now it's a conscious decision to limit, that's all.
Either way we both know what we're talking about and will never see eye to eye on the politics arguing.
Thanks for your help!
February 15, 2013 at 3:10 pm
A. A. (2/15/2013)
Obviously 32 bit OS couldn't "handle" more than that or it would have, and there would not have been a limit. Instead, it let you enable relinquishing control to special programs designed to really handle it.
No, that's not what happened. There were no 'special programs' designed to handle memory that the OS couldn't. Rather additional APIs were added into the OS to circumvent the 32-bit direct addressing limitation. Note, into the OS. There's no politics here, there's just additional APIs in the OS to indirectly address the memory above 4GB, APIs that any application running on that OS could call.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply