April 14, 2014 at 6:48 am
I'm attempting to enable AWE on SQL Server 2005 SP2 Standard but have read there may/may not be other items to enable first depending on the version.
I know I have to first enable lock pages in memory. I have sql running via the local system account so I don't think I need to add any users to the group policy correct? I also read that I need to enable trace flag 845 during startup or else it won't work? And that SP3 with CU4 is required for this to actually function in the Standard edition? There doesn't seem to be any official MSDN documents referencing any of this, but other DBA's mention this process.
Even with none of this done, if I set AWE to enabled, I actually get an "Address Windowing Extensions Enabled" message which contradicts what some of the DBA's are saying is required.
April 14, 2014 at 6:54 am
What's the exact version you're working with (output of 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
April 14, 2014 at 7:22 am
Microsoft SQL Server 2005 9.00.3042.00
April 14, 2014 at 7:23 am
And you left out the main piece I was interested in.:-)
x86 or x64? Is the OS x86 or x64? If you're not sure, just post the output of 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
April 14, 2014 at 7:27 am
It's on a closed network so direct copy and paste is kind of a pain unless its a full log or something. But yeah its x86. Both SQL (standard) and windows server 2003 (standard) are 32bit versions.
April 14, 2014 at 11:07 am
mlwiller2 (4/14/2014)
I'm attempting to enable AWE on SQL Server 2005 SP2 Standard but have read there may/may not be other items to enable first depending on the version.I know I have to first enable lock pages in memory. I have sql running via the local system account so I don't think I need to add any users to the group policy correct? I also read that I need to enable trace flag 845 during startup or else it won't work? And that SP3 with CU4 is required for this to actually function in the Standard edition? There doesn't seem to be any official MSDN documents referencing any of this, but other DBA's mention this process.
Even with none of this done, if I set AWE to enabled, I actually get an "Address Windowing Extensions Enabled" message which contradicts what some of the DBA's are saying is required.
AWE is independent of LPIM. What's your actual issue and how much memory do you have on the server ?
--
SQLBuddy
April 14, 2014 at 11:12 am
4Gb memory total on the sever. I have several databases over 20Gb that I can see SQL can't hold in RAM since the page life expediency time drops to near zero.
It looked like LPIM was required in order to have AWE work correct, but that may only apply to x64 systems? The documentation of differences between x86/x64 for enabling AWE seems pretty vague. However, in my test VM, all I did was enable /PAE and AWE using the GUI and it can see the AWE_Enabled run flag is "1" in the advanced options query.
I was thinking all of the other steps like updating to SP3, running trace flag 845, etc... only applied to x64 versions for AWE but I wanted to make sure.
April 14, 2014 at 11:29 am
mlwiller2 (4/14/2014)
4Gb memory total on the sever. I have several databases over 20Gb that I can see SQL can't hold in RAM since the page life expediency time drops to near zero.It looked like LPIM was required in order to have AWE work correct, but that may only apply to x64 systems? The documentation of differences between x86/x64 for enabling AWE seems pretty vague. However, in my test VM, all I did was enable /PAE and AWE using the GUI and it can see the AWE_Enabled run flag is "1" in the advanced options query.
I was thinking all of the other steps like updating to SP3, running trace flag 845, etc... only applied to x64 versions for AWE but I wanted to make sure.
AWE is useful only on 32 bit systems not on 64-bit ones. And both PAE and AWE will work only if the server memory is > 4GB.
So in your case enable \3GB switch to enable SQL to use upto 3GB of memory. On 32 bit systems, if there are memory pressures then SQL server will be forced to release meomory for the OS to use.
Yeah, LPIM requires SQL Server 2005 SP3 CU4 for std edition.
Basically, LPIM is used to lock SQL Server pages in memory and to prevent paging. AWE is to extend the SQL Server memory usage beyond 4 GB.
For your case even though AWE says enabled, it won't come into affect.
--
SQLBuddy
April 14, 2014 at 11:32 am
Forgot to mention that I had planned on adding an additional 16gb for a total of 20gb to take advantage of AWE. So is LPIM not a requirement for AWE to function (just a recommendation)? All 32bit I'm working with.
I have SQL running using the local system account so what query statement could I use to check and see if LPIM was currently being used?
April 14, 2014 at 11:50 am
mlwiller2 (4/14/2014)
Forgot to mention that I had planned on adding an additional 16gb for a total of 20gb to take advantage of AWE. So is LPIM not a requirement for AWE to function (just a recommendation)? All 32bit I'm working with.I have SQL running using the local system account so what query statement could I use to check and see if LPIM was currently being used?
Sorry, looks like LPIM is needed before enabling AWE. I'm not sure why MS interlinked both of these settings.
So, if you plan on adding 16GB then enable PAE switch, LPIM and then AWE. LPIM helps you preventing the paging of SQL Server.
Use the following query ..
exec xp_readerrorlog 0, 1, 'locked pages'
The O\P of the query should contain the text
Using locked pages for buffer pool
--
SQLBuddy
April 14, 2014 at 11:53 am
mlwiller2 (4/14/2014)
So is LPIM not a requirement for AWE to function (just a recommendation)?
It is a requirement.
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
April 14, 2014 at 11:54 am
I got "0 rows effected." I'm going to upgrade to SP4 on my test VM to see if that could be the issue. If I'm running SQL using the system account I shouldn't need to adjust the user roles right?
April 14, 2014 at 11:56 am
sqlbuddy123 (4/14/2014)
I'm not sure why MS interlinked both of these settings.
Because they're very tightly linked.
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
April 14, 2014 at 12:03 pm
GilaMonster (4/14/2014)
sqlbuddy123 (4/14/2014)
I'm not sure why MS interlinked both of these settings.Because they're very tightly linked.
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.
Thanks Gail. It's bit weird bcs they both do different functions and still MS interlinks them.
--
SQLBuddy
April 14, 2014 at 12:08 pm
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.
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
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply