Setting memory

Question

Post reply

Setting memory

HBK_4700

SSCrazy Eights

Points: 8932
More actions
December 21, 2009 at 10:38 am

#140226

How would I know whether I need to enable AWE for my SQL Server sandard edition
Server RAM 8GB
Server properties-memory settings
Minimum server momery 0MB
Maximum server memory 2147483647 MB
OTHER MEMORY OPTIONS
Index creation memory 0KB
memory per query 1024 KB
Please let me know if you need further details to answer to my question. Thanks!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

32 bit or 64 bit?

If you're not sure, run this and post the output

SELECT @@version

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 2

GilaMonster (12/21/2009)
32 bit or 64 bit?
If you're not sure, run this and post the output
SELECT @@version

@SQLNumpty SSCrazy Points: 2920 More actions · Answer 3

You will need to ensure that you have the /PAE (and maybe /3GB) switch in your boot.ini file and you have set the Enable the Lock Pages in Memory Option.

If you need to add the switches, you need to reboot. Then, just tick the AWE box. Cannot remember if you need to stop and restart the SQL Server service but perfmon will tell you if this is working.

http://www.eraofdata.com/blog/2008/10/sql-server-memory-configuration/

http://msdn.microsoft.com/en-us/library/ms190730.aspx

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 4

Mark_Pratt (12/21/2009)

You will need to ensure that you have the /PAE (and maybe /3GB) switch in your boot.ini file and you have set the Enable the Lock Pages in Memory Option.

If you need to add the switches, you need to reboot. Then, just tick the AWE box. Cannot remember if you need to stop and restart the SQL Server service but perfmon will tell you if this is working.

http://www.eraofdata.com/blog/2008/10/sql-server-memory-configuration/

http://msdn.microsoft.com/en-us/library/ms190730.aspx%5B/quote%5D

If I understood correct..

I need to enable the lock pages in memry option, then check AWE box in server memory settings

please correct me if Iam wrong

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 5

Lynn Pettis

SSC Guru

Points: 442447

December 21, 2009 at 1:37 pm

#1095075

Nevermind, clicked wrong link.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

Mark_Pratt (12/21/2009)
You will need to ensure that you have the /PAE (and maybe /3GB) switch in your boot.ini file and you have set the Enable the Lock Pages in Memory Option.

/PAE yes, /3GB no, not on 8GB of memory.

Using /3GB limits how much memory the kernel can address. As total amount of memory goes up, the kernel needs more memory to manage it. Use /3GB together with /PAE and there's a risk of 'starving' the OS of memory. Not fun.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

If you do use AWE, you need to set SQL's max memory to ensure it doesn't use too much and hurt the OS. Set max to 6.5GB at most.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 8

HBK_4700

SSCrazy Eights

Points: 8932

December 21, 2009 at 1:53 pm

#1095093

So in my case shall I enable AWE ?

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 9

kareem007 (12/21/2009)
So in my case shall I enable AWE ?

If you want SQL Server to be able to access more than 2GB of memory, then yes - you need to enable AWE.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 10

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
If I understood correct..
I need to enable the lock pages in memry option, then check AWE box in server memory settings

In Std. edition SP3+CU-4 only support lock pages in memory option

http://qa.sqlservercentral.com/Forums/Topic803211-525-1.aspx

AWE Disscussion

Edit:Post the URL

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/