question

Sachindj avatar image
Sachindj asked

regarding AWE in SQL 2005

My Server is a Child Domain with Windows Server 2003 EE and SQL Server 2005(Devp. Edition). Recently, I have upgraded the server RAM from 4GB to 16GB. I have the following setting for SQL to utilize the additional memory available. 1. Given rights of 'Lock pages in memory' to the Administrators through Domain Controller Security Policy and Domain Security Policy. 2. Enabled AWE in SQL Server 3. Set the Min memory to 0 and Max Memory to 10240 MB (since I want to use 10GB only now). 4. Set 'Maximum Data throughput to network applications' in the LAN properties 5. Increased the PageFile Size 6. Put the switch /3GB and /PAE in boot.ini file. Inspite of all the above setting, SQL still does not use more than 2.75 GB of RAM (as seen in Task Manager). Please tell me, How can I make SQL utilize 10GB of the available 16GB of RAM. And also when I view DBCC memorystatus it shows AWE Allocated as 0. Thanks in advance.
sql-server-2005memoryawe
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
Memory allocation in SQL Server is not reported properly in Windows when using AWE. You need to take a look at [`sys.dm_os_process_memory` (BOL)][1], this will report memory usage inside SQL Server. SELECT physical_memory_in_use_kb, -- How much memory is being used locked_page_allocations_kb, -- How much is locked by SQL Server (using AWE) total_virtual_address_space_kb, -- Virtual address space size virtual_address_space_reserved_kb, -- reserved VAS virtual_address_space_committed_kb,-- used VAS virtual_address_space_available_kb -- reserved minus used VAS FROM sys.dm_os_process_memory [1]: http://msdn.microsoft.com/en-us/library/bb510747(SQL.100).aspx
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
Sorry - forgot it is one of the dmvs that came with 2008. Try looking at this article: http://msdn.microsoft.com/en-us/library/cc966540.aspx In Particular look at: -- amount of memory consumed by components outside the Bufferpool -- note that we exclude single_pages_kb as they come from BPool -- BPool is accounted for by the next query select sum(multi_pages_kb + virtual_memory_committed_kb + shared_memory_committed_kb) as [Overall used w/o BPool, Kb] from sys.dm_os_memory_clerks where type <> 'MEMORYCLERK_SQLBUFFERPOOL' -- amount of memory consumed by BPool -- note that currenlty only BPool uses AWE select sum(multi_pages_kb + virtual_memory_committed_kb + shared_memory_committed_kb + awe_allocated_kb) as [Used by BPool with AWE, Kb] from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLBUFFERPOOL'
3 Likes 3 ·
Sachindj avatar image Sachindj commented ·
The view 'sys.dm_os_process_memory' does not exist in SQL 2005, what is the option for it in SQL 2005 ? And also when I see DBCC memorystatus it shows awe allocated as 0.
0 Likes 0 ·
Cyborg avatar image Cyborg commented ·
William, can we get the memory utilization of SQL Server using the performance counter Process: Working Set for SQL Server?
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
As I understand it, these performance counters do not account for the usage internally - I may be wrong, but these are the same counters you can see in perfmon (outside sql server) and are the same that show in taskmanager. The dmv I mentioned first (on SQL 2008 only) is brilliant, but doesn't help in this case. I think the only option is to look at the second solution in the comments above and to find some sort of total that fits the needs.
0 Likes 0 ·
Sachindj avatar image Sachindj commented ·
The Dmv that you specified above to check memory gave the following results : Overall used w/o BPool, Kb 26592 Used by BPool with AWE, Kb 2685092 Still doesn't seem to cross the 2.7 GB memory mark... DBCC MEMORYSTATUS still shows the AWE allocated as 0.
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
@Sachindj - can you please re-check that AWE is enabled on this machine? you need to ensure that /PAE is set in the startup variables for windows. After that, please also run the following script: SELECT * FROM sys.configurations WHERE name = 'awe enabled' That will show you if AWE is enabled and active for sql server too.
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
Keep in mind that SQL server doesn't allocate Max memory until its needed to serve your workload.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ozamora avatar image
ozamora answered
Dbcc memorystatus Check for awe allocation Update, seems that your machine is not picking up AWE; make sure the /PAE option is the only one in the boot.ini and go from there. This is the memory utilization that an X86 with AWE should show (in the 200s KB by default): ![alt text][1] [1]: /upfiles/AW.png
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
I agree with the answers above, however my suggestion is to wipe your 2003 EE and install 2003 EE x64 with SQL 2005 x64. Then problem solved the correct way.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
I agree that 64 bit is the way to go with large physical address usage
0 Likes 0 ·
ozamora avatar image ozamora commented ·
Agreed. But sometimes is unfeasible. I have an important cluster on x86 and business cannot afford downtime. I have plans to migrating off to an x64 R2 cluster.
0 Likes 0 ·
DaniSQL avatar image
DaniSQL answered
[Here][1], [Here][2] and [here][3] are the best resources I have ever seen on [/3GB, /PAE and AWE][4]. No more guessing what they do or don't do. I keep it book marked to take a look whenever I am confused(which is frequent btw=). The third article advices to '*Ignore Task manager*' when AWE is enabled and to use Perfmon counter `SQL Server: Memory Manager: Total Server Memory (KB)` to '*get a quick reference on exactly how much memory SQL Server is using at that moment*'. [1]: http://blogs.msdn.com/b/chadboyd/archive/2007/03/24/pae-and-3gb-and-awe-oh-my.aspx [2]: http://blogs.msdn.com/b/oldnewthing/archive/2004/08/22/218527.aspx?wa=wsignin1.0 [3]: http://www.eraofdata.com/blog/2008/10/sql-server-memory-configuration/ [4]: http://blogs.msdn.com/b/oldnewthing/archive/2004/08/22/218527.aspx?wa=wsignin1.0
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ozamora avatar image ozamora commented ·
Agreed. His instance is not picking up AWE as it is showing a high mem usage under the Task Manger. It should be less than 500K.
0 Likes 0 ·
Sachindj avatar image
Sachindj answered
It seems AWE was not configured properly... My problem was finally resolved by reconfiguring AWE and restarting the SQL Service... Thanks all, for your guidance and support. It was of great help to me... Thanks again...
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
If any of the answers helped, please mark one as such (tick the answer).
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.