SQL Server 2005 SE Adressable Memory

Last Post 09 Jan 2008 01:31 PM by TRACEYSQL. 10 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
VenteDBA
New Member
New Member

--
25 Oct 2007 07:29 AM
Hello

I'm reading through various documents trying to discern how much memory SQL Server 2005 32bit SE can actually manage with the various switches (AWE. PAE, /USERVA, /3GB) set.

The problem I'm having is determining whether 2K5 SE can manage memory above 4GB with the /PAE switch added to the boot ini file. I look in BOL, and it states the maximum memory is that which can be supported by the operating system. I also read that only 2K5 EE can use the memory above 4GB with AWE enabled when /PAE is added to boot.ini

Can SQL Server 2K5 use the memory above 4GB for its buffer pool, or do I need to install EE?

Any help would be appreciated.
VenteDBA
New Member
New Member

--
25 Oct 2007 11:10 AM
That's what I infer from BOL, but in the SQL Server 2k5 training kit for Exam 70-443, Microsoft Press, it says "Only SQL Server 2005 EE can use this extra memory made available through PAE; the application must be configured to use AWE."

I'm having trouble reconciling BOL and the training kit.
VenteDBA
New Member
New Member

--
25 Oct 2007 12:21 PM
Hello

Thanks for the info.

I understand the concepts of the memory management at the API and operating system level.

I just want to know if SQL Server Standard Edition with AWE enabled can use the memory above 4GB as one may infer from Books OnLine, or, as it states in the training kit, you need to purchase Enterprise Edition.

If the former is true, I can realize a substantial cost savings. I want to load a machine with 16GB RAM and install SQL 2K5 standard edition, configure max server memory to 12GB. Will the 12GB of RAM be wasted or allocated to the buffer pool?
VenteDBA
New Member
New Member

--
25 Oct 2007 12:55 PM
Hello

Thanks for the links, and yes, I already viewed them.

I referred to them to the company's Windows engineer because he initially refused to believe me when I told him the service account running SQL Server needed the local policy for locking pages in memory. He thought local admin was enough.

VenteDBA
New Member
New Member

--
26 Oct 2007 08:01 AM
Hello

The engineer has seen the light, but we digress. I still have no definitive answer if SQL Server Standard edition will support 16GB of RAM, or do I need to install the Enterprise edition
VenteDBA
New Member
New Member

--
29 Oct 2007 05:41 AM
Hello


Thank you for the answer and I appreciate any time and effort you had expended reasearching it.

Ray
TRACEYSQL
New Member
New Member

--
09 Jan 2008 09:15 AM
hi there,
Just installed SQL2005 Enterprise windows 2003 enterprise.
Does the switch have to be set 4GB with the /PAE switch added to the boot ini file in order to use more then 4GB.

When i shutdown and restart SQL 2005 i see it says 4096MB which means its not using all of the 12 GIG.

Can someone confirm for me.

Thanks
TRACEYSQL
New Member
New Member

--
09 Jan 2008 12:32 PM
SO 32 bit you put in the switch
then in SQL configure to use AWE
in os do this grant lock pages in memory to service account for 32x


In SQL 2005
32 bit and 64 bit do nothing? or do the switch just for 32 bits.

Sorry bit above my head memory switches.

TRACEYSQL
New Member
New Member

--
09 Jan 2008 01:31 PM
This is only for SQL 2005 Enterprise

Have 32 bit SQL and 64 bit productioin

So even for 2005 need to do the switch, my network guy is telling me to do nothing?
TRACEYSQL
New Member
New Member

--
10 Jan 2008 06:37 AM
interesting links thread.

Our test 2005 is 32 bit so i look at the error log and yes i see
SET AWE Enabled to 1 in configuration parameter to allow use of more memory.

Our production is 64 bit, still waiting for this to be built and i should see
something like Lock Pages in Memory

If all reports everything is set up correctly.

If not something is set up wrong on the account that using SQL - Group Policies
I go back and figure that bit out when it does not appear.

So thats it nothing else to do on the SQL Server Part or any ini boot up settings.






TRACEYSQL
New Member
New Member

--
10 Jan 2008 07:04 AM
Put all the steps for Memory

SQL 2005 32 Bit to use more than 4 GIG do this
1. add pae to boot.ini
2. configure lock pages in memory, in group policy , computer configuration expand window settings,
expand security settting, local policies, user right assignments, click lock pages in memory , add
sql account that starts up sql.
3 In sql 2005 memory settings tick flag use AWE.

In error log see SET AWE enabled to 1 in configuration parameter to allow use of more memory.
(Do not see anything in lock pages in memory and only have 4gig so not sure if message should be coming out).

SQL 2005 64 bit
1. No ini files to set
2. configure lock pages in memory, in group policy , computer configuration expand window settings,
expand security settting, local policies, user right assignments, click lock pages in memory , add
sql account that starts up sql
3.. Do not set the awe flag in sql.
Error Log see message "lock page in memory" not sure of exact message not got 64 bit machine yet.

Is that all correct.

Thanks
You are not authorized to post a reply.

Acceptable Use Policy