AWE Enable steps - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

AWE Enable steps

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-11-29 : 22:16:51

Dear Experts,

I am using Windows server 2003 Enterprise Edition build 3790 service pack 2 with 32 Bit with one SQL server 2005 EE instance.

I have a 32GB of RAM.

I need to enable AWE on this server to have to increase to some 20-24GB whats all your recommendations ? Pls guide me for optimal performance.

Below is the output of @@version:

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Below is output of sys.configurations.

name |value| minimum| maximum| value_in_use| description| is_dynamic|is_advanced

awe enabled |1| 0| 1 |0 |AWE enabled in the server |0 |1

max server memory (MB) |12288| 16 |2147483647| 83647 |Maximum size of server memory (MB)| 1 |1

Please kindly suggest how to enable AWE.

Thanks,

Gangadhar

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-11-30 : 11:19:36

Hi. If your AWE value is 1 and your in_use is 0 - try Restaring or Rebouting. We have 1 and 1 for this on our Server.

In case you don't have this query, it is good:
select
*
from
master..sysperfinfo
where
object_name = 'SQLServer:Buffer Manager'

The 'Target Pages' should tell you if it is working. These are the number of 8k pages it can acquire in the buffer pool, so if you multiply that value by 8 you should get into the 20-24GB. You'll also see some AWE readings from this query.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-11-30 : 17:58:59

You should be having 64-bit EE Server if you want to use RAM.

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-12-01 : 02:47:28

Hi Denis,

Thanks for your valuable comments here.
I checked as per your query AWE value is 1 and your value_in_use is 0 from select * from sys.configurations query.
Also i have used this query
select
*
from
master..sysperfinfo
where
object_name = 'SQLServer:Buffer Manager'

and 'Target Pages cntr_value shows the value of 200328 *8 =1602624 its around 1.6 GG,so i can conclude the AWE option is not enabled.

Can you pls suggest the steps to enable this.I have requested Windows team to add /PAE switch to boot.ini file after that what are the configurations which i need to do in SQL server.

Pls help me Thanks.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-12-01 : 09:01:13

Gangadhara,

Good point about the PAE - that is also required.

Since your value for AWE Enabled = 1 and your value_in_use = 0, this means SQL Server wants to go to 1 but it hasn't yet. So I suggest a re-boot of your Server - a re-start of your SQL server may do the trick. Have you done this?

You mentioned your current Target Pages is around 200,000, that was what we had before our AWE enabled allowed us more RAM.

Please let me know how it goes...

Denis

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-12-01 : 11:50:42

quote:
Originally posted by sodeep

You should be having 64-bit EE Server if you want to use RAM.

Not sure what you mean? We used AWE to get SQL Server 32-bit to use additional RAM. Out Total Pages in Buffer Pool is now about 850,000.

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-12-02 : 04:04:18

Hi Denis,

I have restarted with the SQL server services and its showing the same values as before.Do i need to run some queries to get this into effect.
Thanks,
Gangadhar

franco
Constraint Violating Yak Guru

255 Posts

Posted - 2010-12-02 : 05:23:03

Here are the steps to enable AWE and to set max server memory which is advisable you do:

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', xxx (xxx = your number in MB)
RECONFIGURE
GO

You have to restart SQL Server Service and have a look at the error log.
You have to find this string:
"Address Windowing Extensions enabled".

Franco

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-12-02 : 08:57:55

quote:
Originally posted by gangadhara.ms

Hi Denis,

I have restarted with the SQL server services and its showing the same values as before.Do i need to run some queries to get this into effect.
Thanks,
Gangadhar

Have you re-booted the Server itself (ie not just SQL Server)? The PAE switch may not go into effect if you do not Reboot the Server.

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-01-16 : 22:33:39

Hi,
I have enabled AWE with /PAE switch and i ran the below query

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
sp_configure 'max server memory', 29184( your number in MB)
RECONFIGURE
GO
RECONFIGURE
GO

I have 32 Gb of RAM in the production server and in the Error log i am able to see "2011-01-17 11:39:57.99 Server Address Windowing Extensions is enabled. This is an informational message only; no user action is required.
2011-01-17 11:39:58.21 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required."

Please let me know how we can monitor this Is really SQL is using 28GB of RAM or not ??

Thanks,
Gangadhara MS
SQL Developer and DBA

Sachin.Nand

2937 Posts

Posted - 2011-01-18 : 08:03:32

Do you have any other applications running on the database server?

With just 4 GB left for other stuffs(CLR,SQL Jobs) and not to forget the OS I think it is not a fair deal.

PBUH

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-01-19 : 01:56:59

Currently we are not running any other application, but we have scheduled jobs and CLR.

So for that reason i have made total SQL memory to 26GB.

Please let me know any experts here how to confirm whether its using the 26GB of RAM or not.

Thanks,
Gangadhar

Thanks,
Gangadhara MS
SQL Developer and DBA

abhwhiz
Starting Member

37 Posts

Posted - 2011-01-19 : 03:19:29

You can go to Perfmon and check the counters Total Server Memory (Current Usage) and Target Server memory (Actually required)(under SQLServer :Memory manager) to check how much memory is being used.

If you dont have AWE enabled, from Task Manager you can see how much memory SQL Server process (sqlsrver.exe) is using - it will be mostly accurate.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-01-19 : 20:59:48

You are probably missing the local policy for lock pages in memory. This is required for AWE to be enabled in SQL Server. Add your SQL Server service account to this policy and restart SQL Server.

You can verify this in the SQL Server log - which should have a message about AWE right after starting up.

Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources