SQL 2005 64-bit on Windows 2003 R2 Standard 64-bit and AWE/Lock Pages in Memory

Question

SQL 2005 64-bit on Windows 2003 R2 Standard 64-bit and AWE/Lock Pages in Memory

David Rosenberg

SSC Veteran

Points: 261
More actions
November 9, 2007 at 3:28 pm

#183770

Hey Folks -
I'm setting up a new production SQL 2005 64-bit server running on a dedicated Windows 2003 R2 Standard system w/8GB of RAM.
I've enabled the "Lock Pages in Memory" option (http://msdn2.microsoft.com/en-us/library/ms190730.aspx) but when I run dbcc memorystatus all of my AWE counters show 0.
According to http://blogs.msdn.com/sqlprogrammability/:
"On 64 bit machines, execute 'dbcc memorystatus'. If the AWE Allocated memory is 0 then lock pages in memory privilege has not been granted to the service account or it has not taken effect. "
Just searching across the web I find different answers. In some places it clearly says that Lock Pages in Memory only works on W2K3 Enterprise systems. In other places it says that it should work on any Windows 2003 64-bit OS.
Does anyone have any ideas? Assuming we cannot "lock pages in memory" due to the Windows 2003 Standard OS, are there any other memory management recommendations out there?
thanks!

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

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

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 1

Hi,

If you can see following message in the SQL Server Error Log, then "Lock Pages In Memory" is being utilized, other wise possible mis-configuration of Group Policy for account hosting SQL Server Service:

"Using locked pages for buffer pool"

You need to ensure SQL Server has AWE Enabled on instance to allow SQL to take advantage.

Thanks,

Phillip Cox

David Rosenberg SSC Veteran Points: 261 More actions · Answer 2

Hey Phillip -

No, I don't see that in my SQL Server log.

I've verified through gpedit.msc that the SQL Server account does in fact have the "lock pages in memory" permission. I also tried enabling AWE, but that had no effect.

According to what I've found searching, on 64-bit SQL the AWE setting is ingored and the AWE API's are only used if the "lock pages in memory" setting is enabled. The problem is, there's ambiguity over whether or not this setting works on W2K3 Standard.

I'm 100% sure that my local security policy settings are correct, but it's still not working. I'm assuming that the folks who say this works for W2K3 ENTERPRISE only are correct?

-david

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 3

Hi,

Ok, based on my understanding, you are using SQL Server 2005 x64 Edition on Windows 2003 x64 R2 Standard Edition. As SQL Server has access to all memory and does not require use of the AWE API, which was required under 32-bit versions. The only change you need is to ensure "Lock Pages in Memory" is configured to prevent O\S from swapping SQL Server memory to disk.

Here a good article explaining this in detail:

http://blogs.msdn.com/slavao/archive/2005/04/29/413425.aspx

Thanks,

Phillip Cox

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 4

Actually the "Lock pages in memory" option only works with SQL 2005 Enterprise edition. See also this article http://support.microsoft.com/kb/918483

The standard edition simply ignores the setting.

You should also read this: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/05/03/using-64-bit-sql-server-2005-lock-pages-in-memory.aspx

Markus

[font="Verdana"]Markus Bohse[/font]

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 5

Hi,

Marcus is correct, SQL Server 2005 x64 Standard Edition does not support the "lock pages in memory" configuration setting. You can mimic this by setting Min and Max memory to same value, if required.

Thank you,

Phillip Cox

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 6

you must set the maximum memory on your install otherwise you may have problems.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

I think it is better said that if you don't set the maximum memory you WILL have problems. For an 8GB server with nothing else running on it but SQL Server relational engine (no SSIS/SSRS/IIS/AS, etc) you may be ok with 6.5GB as max for SQL Server. I would go down from there with other things running on the box. Monitor the pages/sec perf mon counter fairly routinely to see if things are paging excessively.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

DavidDay22 SSC-Addicted Points: 415 More actions · Answer 8

I have 8G of memory in my 64 bit Standard Edition SQL box. I set the memory minimum and maximum to the same figure, 6656 MB. When I run DBCC memorystatus I still show 8473656 KB VM reserved and VM committed 6992136 KB for node 0 (I only have one instance on this machine). My AWE is 0. Does this show that SQL has taken all but about 1.5 G of memory? I am running this on W2K3 Enterprise Edition 64 bit OS?

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 9

if you query sys.dm_os_performance_counters you'll get a true figur eon memory usage in a much more easy to read format.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

tavery_scigaj SSC Enthusiast Points: 104 More actions · Answer 10

We also had an issue with this and recently Microsoft has added a way to use Lock Pages in Memory with the 64 bit Std edition of SQL.

You must install Service Pack 3 for SQL 2005 and then apply Cumulative update rollup 5. This allows you to set a trace flag that allows SQL to lock pages in memory.

The explanation is in the Microsoft rollup 5 article.