September 19, 2011 at 10:58 am
Hi All,
Server Stats: Physical Server having Win 2003 SP2 server + SS 2005 Standard Edition SP2 (9.00.3042) + 12 GB RAM + /PAE already set
Want to increase the usage of RAM by SQL Server
Actions already taken:
1. Enabled AWE on server
2. Enabled settings to Lock Pages by SQL Server account using GP Editor
3. Set Minimum Memory Usage=2048 MB & Maximum Usage=10240 MB & left 2 GB for OS
Restarted server, now when checking using dm_os_memory_clerks but showing column 'awe_allocated_kb' values as 0.
Please suggest where exactly lacking for enabling it. Kindly help.
Regards,
Ankur
September 19, 2011 at 11:25 am
32 bit or 64 bit (OS and SQL)?
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
September 20, 2011 at 2:26 am
Hi,
Sorry forget to mention it...it's 32 bit system, madam.
Regards,
Ankur
September 20, 2011 at 10:21 am
Which edition of Windows 2003 SP2 are you using?
Also, could you post the partition details from the boot.ini file on that server too?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 21, 2011 at 8:24 am
Hi Perry,
We have Windows 2003 Enterprise Edition with SP2. Contents in Boot.ini are mentioned below:-
[boot loader]
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS /PAE
timeout=30
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /sos /bootlog /noexecute=optout /fastdetect /PAE
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="HP SMP Preserved:Windows Server 2003, Enterprise" /sos /bootlog /noexecute=optout /fastdetect /PAE
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="HP SMP Preserved:Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /PAE
Let me know if you have any other query & thanks for the response.
Regards,
Ankur
September 21, 2011 at 10:27 am
ankur_libra (9/21/2011)
[boot loader]default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS /PAE
timeout=30
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /sos /bootlog /noexecute=optout /fastdetect /PAE
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="HP SMP Preserved:Windows Server 2003, Enterprise" /sos /bootlog /noexecute=optout /fastdetect /PAE
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="HP SMP Preserved:Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /PAE
With the DEP options set (/noexecute=optout) you dont need the /PAE switch, DEP automatically enables PAE.
Are you sure you have set the locked pages in memory local security policy setting for the correct user account?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 21, 2011 at 11:10 am
Hi Perry,
So you mean to say, that in Boot.ini file:-
--------------------------------------------------------------------------------
[boot loader]
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS /PAE - ONLY HERE /PAE IS REQUIRED
timeout=30
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /sos /bootlog /noexecute=optout /fastdetect /PAE - HERE /PAE IS NOT REQUIRED
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="HP SMP Preserved:Windows Server 2003, Enterprise" /sos /bootlog /noexecute=optout /fastdetect /PAE - HERE /PAE IS NOT REQUIRED
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="HP SMP Preserved:Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /PAE - HERE /PAE IS NOT REQUIRED
Please correct me if I am wrong?
Yes, I have already set the locked pages in memory local security policy setting using the correct user account. On enabling AWE however I got error
"Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process."
But after that when I checked it shows that AWE is enabled. Currently it is showing AWE as enabled.
Also is DEP refers to Data Execution Prevention? If so, on server it is enabled for all programs.
Kindly guide me, how to proceed further..
Thanks,
Regards,
Ankur
September 21, 2011 at 12:03 pm
ankur_libra (9/21/2011)
Hi Perry,So you mean to say, that in Boot.ini file:-
--------------------------------------------------------------------------------
[boot loader]
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS /PAE - ONLY HERE /PAE IS REQUIRED
if you have no DEP options set then yes
ankur_libra (9/21/2011)
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /sos /bootlog /noexecute=optout /fastdetect /PAE - HERE /PAE IS NOT REQUIREDmulti(0)disk(0)rdisk(0)partition(1)\WINDOWS="HP SMP Preserved:Windows Server 2003, Enterprise" /sos /bootlog /noexecute=optout /fastdetect /PAE - HERE /PAE IS NOT REQUIRED
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="HP SMP Preserved:Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /PAE - HERE /PAE IS NOT REQUIRED
Please correct me if I am wrong?
not required here as you have specified DEP options which automatically enable PAE
ankur_libra (9/21/2011)
On enabling AWE however I got error"Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process."
this indicates that the policy has not been applied correctly. Login to SSMS and check the SQL Server log. If AWE is correctly enabled there will be a message in the log indicating that Address Windowing Extensions are in use
ankur_libra (9/21/2011)
Also is DEP refers to Data Execution Prevention? If so, on server it is enabled for all programs.
yes it does, that's fine
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 22, 2011 at 4:49 am
Hi Perry,
Thanks again for providing response. 🙂
When I checked error log, there is only one message related to AWE i.e.
" Set AWE Enabled to 1 in the configuration paramenters to allow use of more memory"
& there is NO message stating that "Address Windowing Extensions are in use"
But as stated earlier, when checking value of AWE using Sp_configure, it is showing as 1. Does it mean that AWE is not actually enabled on server...it's giving fake value ?
Also kindly suggest how to proceed step by step so that AWE can be implemenetd successfully.
Regards,
Ankur
September 22, 2011 at 6:52 am
I believe Books Online has good instructions on how to enable AWE and large memory access on 32 bit systems. I would also look at dbcc memorystatus (documented online) for very detailed information about memory usage.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 22, 2011 at 10:16 am
Have you followed this article - http://msdn.microsoft.com/en-us/library/ms190673(v=sql.90).aspx
Have you looked at the error logs to see - "Address Windowing Extensions enabled."
September 22, 2011 at 11:06 am
I believe the quote below is pretty descriptive of your problem
ankur_libra (9/21/2011)
On enabling AWE however I got error"Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process."
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 17, 2011 at 4:40 am
Hi Perry,
You are right...again checked server..last time server rebooted but no gains...then I remove & reset the 'lock pages in memeory' and 'Sp_configure AWE' settings.
Last saturday, when again server rebooted...hurray...got AWE enabled message in Error log and now it's working perfectly,
Thanks to you and all others for their valuable support. 🙂
Regards,
Ankur
October 17, 2011 at 3:20 pm
Speaking directly to that, in case others run into it...
You must ensure that the security context of the process running SQL Server has the right "Lock Pages in Memory" on the host. In more simple terms the account that runs the SQL Server service on the host needs this right or AWE will not function. The reason I reiterate this here explicitly is for those of you that work in more secured environments, with more robust Active Directory configurations, namely more specific Group Policy configurations. This, like any other internal host right, can be dictated by group policy.
I've seen numerous situations where GPO* propigation that wasn't ran up the flag pole for evaluation by all concerned parties, or other similar situations result in this right being pulled out from under SQL's service account, breaking this.
Just something to be aware of.
*Group policy is probably way off scope for this forum. I won't clutter it with discussion of GP here. I can of course be contacted out of thread.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply