Solved

AWE, PAE and /3GB settings

Posted on 2009-06-29
15
970 Views
Last Modified: 2012-05-07
Hi...

I have read all the posts on AWE and PAE settings, but none of them quite answer my question...

First let me outline the context:

Windows Server 2003 R2 Standard Edition
SQL Server 2000 Standard Edition with SP4
Xeon 2.39GHz, 4GB RAM

Our SQL database is proving too large for the standard memory allocation, so it has been recommended that I explore the possibilities of increasing the amount of memory that SQL uses.

As a result, I have added the 3GB switch to the Boot file -

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Standard" /noexecute=optout /fastdetect/3GB

And I have run the script:

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 3072
RECONFIGURE
GO

When restarting the SQL service, the error "Cannot use Address Windowing Extensions because lock memory privilege was not granted." shows, but I have added my windows account (we don't use specific SQL logins) to the policy list, along with the System account.

Can anyone tell me what I need to do now?

With thanks.
0
Comment
Question by:WaterAid
  • 6
  • 4
  • 4
  • +1
15 Comments
 
LVL 18

Expert Comment

by:brejk
ID: 24735252
Have you restarted the entire server after setting the lock pages in memory permission?
0
 
LVL 18

Expert Comment

by:brejk
ID: 24735257
BTW, the account that has to have Lock pages in memory permission should be the account used to run SQL Server service.
0
 
LVL 18

Expert Comment

by:brejk
ID: 24735263
One more thing: I'm not sure but does SQL Server 2000 Std Edition support up to 2GB?
0
 

Author Comment

by:WaterAid
ID: 24735381
Hi...  Yes the server has been restarted a few times since - I actually implemented the changes over a week ago.  

With regard to the accoutn that is used to run SQL Server - when logging onto the server, it is my network Windows account that is used (I am not a network adminstrator, just a data analyst that has to look after his own hardware as we don't have any SQL IT apecialists in-house!)...  I have added this AND the System account to the Lock pages policy.

I was told that we could use more of our memory capacity, even though this is the standard edition.  If this is not the case, then that would of course simplify things!

Thanks.
0
 
LVL 76

Assisted Solution

by:arnold
arnold earned 300 total points
ID: 24735627
http://www.sql-server-performance.com/articles/per/awe_memory_sql2000_p1.aspx
http://msdn.microsoft.com/en-us/library/aa213764(SQL.80).aspx

You need to use a user login for the sql services that has been granted the lock pages in memory through a GPO (computer settings\windows settings\security settings\local policies\user rights assignment: Lock Pages in Memory). You could grant local system lock pages in memory, but you run the issue that other services on your system that run under the Local system account may reach into that pool.

In a 4GB system allocating 3GB to MS SQL is not advisable since you will leave only 1GB for everything else.  You would be better off adding additional memory into the system.

Bringing the system up to 8GB or 16GB might be a better course of action with a better effect.

Just allocate around 75%-80% of RAM to AWE.
0
 

Author Comment

by:WaterAid
ID: 24735728
Hi arnold...

Thanks for your answer - I'm prepared to run a risk on this server as SQL is the only application running (1 user), so I wouldn't anticipate the OS needing more than the 1GB...  

With regard to the Lock Pages permission - I have assigned myself (as a network login) those rights but I still get the permissions error.

I've attached a snapshot of the properties window for the Lock Pages in Memory option...  I login to the server as WATER/simonca.

Any further thoughts?

And if I get the AWE option working, will SQL then take advantage of the full 3GB?

Thanks


GPO.bmp
0
 
LVL 76

Expert Comment

by:arnold
ID: 24735848
Is your account used by sql services to start/run?

You need to assign this right to the login used to start SQL services (check services in administrative tools).


You could try using the /3gb option to see whether the 1gb/3gb split will work.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:WaterAid
ID: 24736366
The login used to start SQL services is simply the default Local System account...  

I have already activated that /3GB option, but SQL still only uses a maximum of 1.7 GB of the system memory.
0
 
LVL 76

Expert Comment

by:arnold
ID: 24736848
I think the issue is that the OS takes up the 2GB Virtual Memory.  This is discussed in the msdn link I posted earlier.

You should consider using a user under whose credentials the sql server runs.  Prior to making the adjustment, make sure that user has full rights on the SQL's database location directory.
You should have all sql server's services running under those credentials.
Assign the lock pages in memory rights to this user and then see if you have better success.
I do not think you will see any detectable benefit of transition a 4GB system to AWE.
A detectable benefit will likely be seen if you use AWE on a larger memory system where you have 2GB for the OS and the remainder for the SQL service.  How big is the database?
0
 
LVL 18

Expert Comment

by:brejk
ID: 24737069
I'm pretty sure SQL Server 2000 Standard Edition supports only up to 2GB of RAM. So adding more memory will not be a benefit here.
0
 

Author Comment

by:WaterAid
ID: 24737146
The database is 50GB...  Unfortunately, we recently reached a critical mass table-wise that means that queries are too large for the RAM to process without loads of paging...

I've been reading a lot around the forums and there seems to be a lot of conflicting advice regarding memory gains to be had using switches when running SQL 2k STd Ed and Windows Server 2003 with 4GB or less...  It would be good to get a definitive answer - even the gurus seem unclear!
0
 
LVL 7

Accepted Solution

by:
wilje earned 200 total points
ID: 24737426
There is no reason to even attempt to enable or use AWE on a system with only 4GB of memory.  AWE (Address Windowing Extension) address memory above the 4GB limit for 32-bit systems.
If you increase the available memory on the system, then you would need to worry about that option.  However, in your configuration it should already be utilized - because SQL Server is running as the local system account.  The local system account has this privilege by default.
Setting /3GB switch in boot.ini would allow SQL Server access up to 3GB of memory leaving 1GB for the O/S.  However, this option is not available to you either - because you are running SQL Server 2000 Standard Edition and this editon can only use 2GB of memory.
Your options are to upgrade to SQL Server 2005/2008 (I would recommend just going to 2008) as these version can use all memory that is available from the host.  I would also recommend upgrading to x64 and forgetting about setting /3G /PAE (AWE) and just worry about setting maximum memory for SQL Server (required on x64 systems).
0
 

Author Comment

by:WaterAid
ID: 24737535
Thanks for your feedback guys...  Unfortunately, our front-end software is only compatible with SQL 2000, so it seems I'll have to roll with poor performance til that is upgraded later in the summer.

Thanks again.
0
 
LVL 76

Assisted Solution

by:arnold
arnold earned 300 total points
ID: 24738078
If you have a development setup, where you can test whether the application you use will work with the database running under sql2005 in sql2000 mode, this could help in addressing.

Compatible often means that this is the Version the application was tested on and what the vendor will support.  The possibility that it could work with sql 2005/2008 with the database in sql2000 is possible.

If you are not planning on discarding the HW you currently have your sql2000 on, an option could be to add more memory to see whether the updates/SPs released since allow/take advantage of the larger space along with the use of AWE if possible.

I've seen application vendors default to answering 'No' just because their QA did not evaluate a particular setup either because it was unavailable or the HW costs at the time limited the scope of possible users.


0
 

Author Comment

by:WaterAid
ID: 24745529
Thanks again for your feedback - it seems there's little I can do for now, so I'll put aside some time for a SQL 2005 test.

0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL 2014 get SPIDs of users 6 27
SQL Mirror and Replication 5 27
Help with SQL Query 23 39
Update in Sql 7 12
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now