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.
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
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'
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.
Keep in mind that SQL server doesn't allocate Max memory until its needed to serve your workload.
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
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.
[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
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...
