November 9, 2007 at 2:31 am
Hi again
You're right when you say "I have since read that best practice is to have both nodes identical" that is even "very" best practice.
1 . No you don't need 64-bit OS to use more than 4GB RAM for SQL Server.
I might be wrong on this one, I think you can to use more than 4GB in SE with AWE in-place and PAE and/or /3GB switch in boot.ini.
You get an easier administration and possibly more stable memory management with a 64-bit OS and APP.
And you don't have worry about /PAE, AWE or /3GB switches. You can use AWE with 64-bit.
Windows 2003 SE(64-bit i.e. x64) supports up to 16 GB RAM or possible more.
2. No I don't think you can have different settings, because when you use awe that is a setting in sql server (sp_configure, awe enabled).
I see that you understand why, in failover situation you would have big problems...to say the least..
3. Yes right again.
So your best choice i guess is to stay with your current system and buy an addtional 4 Gb RAM and your good to go.
Or reinstall to x64 versions of windows 2003 SE and sql server 2005 SE with the addtional 4 GB RAM for easier setup and better memory management.
/Mekal
November 9, 2007 at 2:57 am
Hi everyone,
For a detailed explanation of the differences between SE and EE (CPU, memory, clustering, etc.) please see this page:
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
This link explains the memory support and AWE configuration of SQL:
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx#EYIAC
and here too: http://msdn2.microsoft.com/en-us/library/Aa366718.aspx
Regards,
Brandon
November 9, 2007 at 6:30 am
enabling the 3GB switch is not always the best way to go as it restricts lower memory in 32 bit editions.
You can use awe without the 3gb switch with 4gb of ram.
if you enable awe you must set max memory value. My understanding is awe disables dynamic memory and you can set what you like on sql server but the only setting which is actually used is the max memory one. Always allow enough memory for the o/s and monitor for any problems.
Using the 3gb switch may force you into using the -g startup option.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
November 10, 2007 at 7:18 am
mekal inc (11/9/2007)
Hi again...
So your best choice i guess is to stay with your current system and buy an addtional 4 Gb RAM and your good to go.
Or reinstall to x64 versions of windows 2003 SE and sql server 2005 SE with the addtional 4 GB RAM for easier setup and better memory management.
/Mekal
I just found out some good news. It turns out I was wrong about this cluster. It does in fact have 8 GB of RAM for each of the 2 nodes.
Given that, and given that we have 32-bit Win 2003 Server Enterprise Ed. and 32-bit SQL 2005 Standard Edition, I have these 2 questions (I hope my last in this thread):
1. How can I enable the servers to have SQL 2005 use 5 or 6 GB of RAM? Is that possible?
2. How can I tell that SQL 2005 is in fact using the 5 or 6 GB of RAM? Does it grab the RAM immediately or do I need to do some kind of load test to make it use the extra RAM?
Sorry to ask for step-by-step directions (or as close to step-by-step as possible), but I want to make sure I get it right.
Thanks to everyone for all of their help.
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 10, 2007 at 2:06 pm
1. How can I enable the servers to have SQL 2005 use 5 or 6 GB of RAM? Is that possible?
Enable the /PAE switch in boot.ini if it's not already there, on both servers. Use 3GB switch if you want to use the absolute max RAM = 7 GB for sql server. Enable AWE in sql server and set min and max values to want you 5 GB = 5120, 6 GB = 6144 and so on..Don't use /3GB switch if you're not going to use 7 GB for sql server.
2. How can I tell that SQL 2005 is in fact using the 5 or 6 GB of RAM? Does it grab the RAM immediately or do I need to do some kind of load test to make it use the extra RAM?
No it does not grap all memory immediately, when load hits it will start allocating memory.
Don't be sorry, just glad to help..we are all in the same boat..
Good luck.
/Mekal
April 8, 2008 at 4:53 am
Hi reading through all this has me a little confused....:unsure:
I have Windows Server 2003 EE, 4GB Ram, SQL Server 2005 SP2.
SQL currently using around 1.6Gb, we need to allow it to use 2.5 - 3Gb of RAM.
After seeing and reading all the artucles i have not found what steps are required especially regarding the the /3GB /PAE switches.
So far before i attempt this i believe i have to perform these;
1. sql account - added to 'Lock Pages in Memory' policy
2. The Maximize data throughput for network application
But do i need to set /3GB switch on this server? or an i just enable AWE and set the Min/Max values??
Any help greatly appreciated.
Oraculum
April 8, 2008 at 5:35 am
I believe that lock pages is required if you're using AWE. If you don't have more than 4GB memory on the server, you do not need awe or /PAE
As it stands, SQL can use up to 2 GB of that memory, and the OS (kernal) can use up to 2GB.
If you enable /3gb, that changes to 3GB for SQL, 1 GB for the OS.
You should not use both /Pae and /3GB at the same time, especially if you have a lot more than 4GB memory. It can lead to the kernal being starved fro memory and, potentially, crashing.
Make any more sense?
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
April 8, 2008 at 5:41 am
Yes thats helps... we have another sneaky problem as the server is only reporting 3.2GB of RAM i have found this article http://support.microsoft.com/kb/929580/
bearing this is mingd if i set the /3GB switch - can i limit sql to 2.5GB using the max memory usage value? as with the OS only reporting 3.2GB out of 4GB i dont wont to only have 0.2GB of RAM for the OS (kernel)?
Oraculum
April 9, 2008 at 3:14 am
There has been a lot of incorrect information posted in this thread about /3GB and /PAE!
Colin Leversuch-Roberts spends his (working) life fixing SQL performance issues. If you want to know more about tuning memory, look at his other posts.
A good article about what PAE is at http://en.wikipedia.org/wiki/Physical_Address_Extension. Also Google for MS KB articles about this, they are normally correct. If you are running a 32-bit OS, then you need the /PAE switch to access more than 4 GB memory. 64-bit Windows does not support PAE.
A good article about the /3GB switch is at http://msdn2.microsoft.com/en-us/library/ms791558.aspx. The /3GB switch should only be used for 32-bit OS servers having between 4GB and 16GB memory, but it is not always beneficial. Colin has a lot of good advice about when it is good to use, when it should not be used, and some problems you might find when using it.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 9, 2008 at 3:21 am
The Practical troubleshooting book (edited by Ken Henderson) has a chapter dedicated to memory, including when and why to use the switches and exactly what effect they have. It's a very good read.
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
April 10, 2008 at 4:51 am
As Gilamonstor stated, if you are only using 4gb, you don't need to use AWE or the switches. Just set your min & max memory levels for SQL:
sp_configure 'min server memory', 1024
RECONFIGURE
GO
sp_configure 'max server memory', 2580
RECONFIGURE
GO
If you go above the 4gb 32bit limit, then you need to enable AWE:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
Otherwise you should be all set. If you see that SQL is only using 1.6gb of memory, then it must not need any more at that time. SQL will grab more memory up to the max limit as it requires it.
HTH,
John
April 10, 2008 at 5:13 am
laker_42 (4/10/2008)
As Gilamonstor stated, if you are only using 4gb, you don't need to use AWE or the switches. Just set your min & max memory levels for SQL:
That's not quite what I said.
Without any of the switches, SQL can only use 2GB (assuming 32 bit SQL on a 32 bit OS)
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
April 10, 2008 at 9:10 am
GilaMonster (4/10/2008)
laker_42 (4/10/2008)
As Gilamonstor stated, if you are only using 4gb, you don't need to use AWE or the switches. Just set your min & max memory levels for SQL:That's not quite what I said.
Without any of the switches, SQL can only use 2GB (assuming 32 bit SQL on a 32 bit OS)
Hi again,
Sorry if I am asking the same question as someone else, but here it is:
I started this thread a while ago, and several people combined to get me the correct information for setting up SQL 2005 Standard Edition with 64-bit (thanks again!), but I am now wondering the following:
If someone wants to use SQL 2005 Standard Edition SP2 32-bit on a server with Windows 2003 Enterprise Edition SP2 32-bit with 16 GB RAM, will SQL 2005 SE use that memory automatically, or do I need to set any switches or change database options? If settings do need to change, what would those settings be? For example, what if someone wants to let SQL 2005 SE use 12 GB or 14 GB and leave the rest (4 GB or 2 GB, respectively) for the OS?
Also, I have read that the "Lock Pages in Memory" option does nothing in SE, only in EE (for example, see http://msdn2.microsoft.com/en-us/library/ms190730.aspx). What does that difference mean in practical terms? Does it affect how much memory SE can use? Is it worse to set Lock Pages in Memory in SE or does it really just make no difference one way or the other?
Thanks again!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 11, 2008 at 2:41 am
In 32-bit mode, SQL Server 2005 SE can use AWE along with Windows /PAE switch to use memory above the 4 GB line.
Only the buffer cache can use memory above the 4GB line. All other functionality lives below the 4 GB line. This means some heavily loaded systems may have a memory bottleneck with stuff that has to live below 4GB while the buffer cache is still reporting 98% to 99% hit rate.
If you use the Windows /3GB switch, then SQL can use up to 3 GB of the sub-4GB memory, otherwise it can only use 2 GB. You should reasearch if /3GB is likely to benefit you. Ultimately you will need to run your production system for a while to see if adding or removing /3GB gives you the best results.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 11, 2008 at 9:00 am
EdVassie (4/11/2008)
In 32-bit mode, SQL Server 2005 SE can use AWE along with Windows /PAE switch to use memory above the 4 GB line.Only the buffer cache can use memory above the 4GB line. All other functionality lives below the 4 GB line. This means some heavily loaded systems may have a memory bottleneck with stuff that has to live below 4GB while the buffer cache is still reporting 98% to 99% hit rate.
If you use the Windows /3GB switch, then SQL can use up to 3 GB of the sub-4GB memory, otherwise it can only use 2 GB. You should reasearch if /3GB is likely to benefit you. Ultimately you will need to run your production system for a while to see if adding or removing /3GB gives you the best results.
Thank you so much for the detailed reply.
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply