Enabling LPIM and AWE

Question

Enabling LPIM and AWE

Viewing 15 posts - 16 through 30 (of 37 total)

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

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 1

GilaMonster (4/14/2014)
sqlbuddy123 (4/14/2014)
It's bit weird bcs they both do different functions and still MS interlinks them.
No, they don't do different things. MS interlinks them because they're actually the same thing under the hood.

LPIM locks the SQL Server Pages. AWE extends the SQL Server memory. How are they related ?

--

SQLBuddy

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

I just explained that

AWE memory is allocated using the AllocPhysicalMemory API call (I'll get you the actual API function name if you want). Only applications with the locked pages in memory privilege have permission to call the AllocPhysicalMemory API
Hence just enabling AWE tells SQL to please use the AllocPhysicalMemory API call instead of VirtualAlloc, but unless you enable locked pages in memory, the SQL process does not have permission to call that API.

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

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 3

My question was Why is this dependency and how can this be justified ?

GilaMonster (4/14/2014) Only applications with the locked pages in memory privilege have permission to call the AllocPhysicalMemory API

--

SQLBuddy

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

mlwiller2 (4/14/2014)
But yeah its x86. Both SQL (standard) and windows server 2003 (standard) are 32bit versions.

Unfortunately, with Windows 2003 Standard 32 bit the OS will not support more than 4GB RAM, you would need to upgrade to Windows 2003 Enterprise edition 32 bit or higher.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

sqlbuddy123 (4/14/2014)
My question was Why is this dependency and how can this be justified ?

When the Windows Engineers were working initially on AWE for 32-bit processes they implemented it with a different API call so that only apps which knew how to handle the higher address spaces could use it, and because it's not going through the virtual memory manager, memory allocated with that API couldn't be paged out. So any memory allocated with the AWE APIs was locked in memory and required a special permission before an app could use it (you're bypassing some of Window's memory management, that's something which has to be explicitly granted to a process)

Later on, that 'can't be paged out' became a feature in itself and so the AWE memory allocation mechanism became use for preventing paging out, rather than it's original usage of allowing access to memory above the 4GB limit of 32-bit process address space and after the switch to 64-bit happened, there was no longer a need to accessing memory above what the process's address space could map (it's a ridiculously large number for a 64-bit process) and so the AWE APIs became solely for allocating memory which can't be paged out.

When you grant a process 'locked pages in memory', it uses the AWE APIs to allocate memory. It's not a dependency, the two are intricately linked. One is the API used, one is a permission required to use that API.

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Perry Whittle (4/14/2014)
mlwiller2 (4/14/2014)
But yeah its x86. Both SQL (standard) and windows server 2003 (standard) are 32bit versions.
Unfortunately, with Windows 2003 Standard 32 bit the OS will not support more than 4GB RAM, you would need to upgrade to Windows 2003 Enterprise edition 32 bit or higher.

And with only 4GB of memory on the server, I typically don't go above 2GB allocated to SQL Server. I also don't typically enable LPIM with just 4GB.

Some say to still enable LPIM with just 4GB, and you can do it. I'd recommend upgrading OS and memory as Perry mentioned.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 7

GilaMonster (4/14/2014)
sqlbuddy123 (4/14/2014)
My question was Why is this dependency and how can this be justified ?
When the Windows Engineers were working initially on AWE for 32-bit processes they implemented it with a different API call so that only apps which knew how to handle the higher address spaces could use it, and because it's not going through the virtual memory manager, memory allocated with that API couldn't be paged out. So any memory allocated with the AWE APIs was locked in memory and required a special permission before an app could use it (you're bypassing some of Window's memory management, that's something which has to be explicitly granted to a process)
Later on, that 'can't be paged out' became a feature in itself and so the AWE memory allocation mechanism became use for preventing paging out, rather than it's original usage of allowing access to memory above the 4GB limit of 32-bit process address space and after the switch to 64-bit happened, there was no longer a need to accessing memory above what the process's address space could map (it's a ridiculously large number for a 64-bit process) and so the AWE APIs became solely for allocating memory which can't be paged out.
When you grant a process 'locked pages in memory', it uses the AWE APIs to allocate memory. It's not a dependency, the two are intricately linked. One is the API used, one is a permission required to use that API.

AWE allows a process to access memory outside it's VAS , but still it's not direct memory access and needs to be mapped to VAS to be used by SQL Server. It's not paged out because AWE memory can't be shared among other processes (not part of working set) and has to be locked.

AWE needs LPIM to be enabled but not vice versa (Ex: 64-bit systems).

Now the question is Does LPIM locks only AWE-SQLPages and not Non-AWE-SQLPages ? This is the wierdness introduced by MS by interlinking them. They should have handled this unambiguously.

--

SQLBuddy

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

sqlbuddy123 (4/14/2014)
AWE needs LPIM to be enabled but not vice versa (Ex: 64-bit systems).

Not true. Once Locked Pages is enabled, memory allocations can be done via the AWE mechanisms, which make them nonpagable. This is the same on 32 bit and 64 bit.

AWE needs the locked pages permission. Locked pages on its own does nothing other than allow a process to allocate memory using the AWE APIs rather than the default VirtualAlloc

Now the question is Does LPIM locks only AWE-SQLPages and not Non-AWE-SQLPages ?

The locked pages privilege allows a process to call the AWE APIs to allocate memory. Memory allocated via the AWE APIs is non-pagable. Memory allocated via the normal VirtualAlloc API calls is pagable.

If you still aren't following, hunt down and watch a series of videos from Mark Russinovich on Windows Memory internals. They're complex, will probably need a few viewings to pick up everything.

Edit: I suspect the confusion may be caused by thinking of AWE just as the 'awe enabled' setting in SQL. That's just a SQL setting, AWE is part of Windows memory management.

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

mlwiller2 SSChasing Mays Points: 647 More actions · Answer 9

Lots of good info here. Unfortunately, I'm still working on getting the output of that statement on page 1 to say "locked pages in buffer pool" correctly (still 0 rows right now). I'm working on some install issues with the SP4 update but will try it again once completed.

If anyone has ever seen the database services update fail on the SP4 install (something about not having admin permissions to rename mssqlsystemresource1.ldf) for error 29538 let me know.

george sibbald SSC Guru Points: 104210 More actions · Answer 10

mlwiller2 (4/15/2014)
Lots of good info here. Unfortunately, I'm still working on getting the output of that statement on page 1 to say "locked pages in buffer pool" correctly (still 0 rows right now). I'm working on some install issues with the SP4 update but will try it again once completed.
If anyone has ever seen the database services update fail on the SP4 install (something about not having admin permissions to rename mssqlsystemresource1.ldf) for error 29538 let me know.

IIRC

make sure you run the installer by using right click 'run as administrator' if your OS has that option

go to the directory where the resource database files are held and explicitly grant the SQL service account full rights to it

---------------------------------------------------------------------

mlwiller2 SSChasing Mays Points: 647 More actions · Answer 11

I'm still getting that error but when doing select @@version it says I'm at 0.0.5000 (sp4). Tried the exec xp_readerrorlog command and still didn't get the "locked memory in pages" output...

mlwiller2 SSChasing Mays Points: 647 More actions · Answer 12

I think I found my own answer:

http://blogs.msdn.com/b/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-privilege-for-local-system.aspx

Since I was always able to see the "Address Windowing Extension enabled" message before upgradeing SQL, it looks like the whole thing about updating to SP3 only applied to 64-bit versions. I also don't see "locked pages in memory" message as that only seems to apply to 64-bit systems.

george sibbald SSC Guru Points: 104210 More actions · Answer 13

If your service pack reported an error I would assign those permissions and re-run it if I was you.

It may appear to hang together as its the log file that failed to get renamed.

you may have to go down to the level of assigning permissions to the file itself

---------------------------------------------------------------------

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

mlwiller2 (4/15/2014)
I'm still getting that error but when doing select @@version it says I'm at 0.0.5000 (sp4). Tried the exec xp_readerrorlog command and still didn't get the "locked memory in pages" output...

Traceflag 845 enabled? (I can't remember if that's required for 32 bit or only 64 bit)

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

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 15

GilaMonster (4/15/2014)
sqlbuddy123 (4/14/2014)
AWE needs LPIM to be enabled but not vice versa (Ex: 64-bit systems).
Not true. Once Locked Pages is enabled, memory allocations can be done via the AWE mechanisms, which make them nonpagable. This is the same on 32 bit and 64 bit.
AWE needs the locked pages permission. Locked pages on its own does nothing other than allow a process to allocate memory using the AWE APIs rather than the default VirtualAlloc
Now the question is Does LPIM locks only AWE-SQLPages and not Non-AWE-SQLPages ?
The locked pages privilege allows a process to call the AWE APIs to allocate memory. Memory allocated via the AWE APIs is non-pagable. Memory allocated via the normal VirtualAlloc API calls is pagable.
If you still aren't following, hunt down and watch a series of videos from Mark Russinovich on Windows Memory internals. They're complex, will probably need a few viewings to pick up everything.
Edit: I suspect the confusion may be caused by thinking of AWE just as the 'awe enabled' setting in SQL. That's just a SQL setting, AWE is part of Windows memory management.

I think you are still missing my point. LPIM behaves differently on 32 bit and 64 bit systems and so is the AWE API. It's complex and lot of analysis is needed to get the true meaning. Please dig deeper into it. Will leave it to you ..

--

SQLBuddy