March 2, 2012 at 8:49 am
Hi , all.
Please help me resolved problems with available AWE allocate for SQL.
SQL Server no used all available memory
We have Microsoft SQL Server 2008 Enterprise R2 (SP1) - 10.50.2772.0 (Intel X86) on Windows Server 2003 Enterprise R2 32Bit with those
hardware specification:
HP ProLiant DL380 G5
2x QuadCore Intel Xeon E5450, 3000 MHz
memory – 12 Gb (DDR2-667 Fully Buffered ECC DDR2 SDRAM)
Settings:
SQL:
Enable AWE Memory for SQL Server
Maximum server memory (in MB) - 11 000 Mb
Minimum server memory (in MB) - 11 000 Mb
WinServer2003R2 Ent
Enable the Lock Pages in Memory Option
In Task Manager available 12 Gb memory, but SQL used only 1,5-1,8 Gb memory.
Performance monitor - SQLServer:Memory Manager_Total Server Memory (KB) = 100%
sql query :
select cntr_value/1024 [memory, mb] from sys.sysperfinfo
where counter_name = 'Total Server Memory (KB)'
Resault:
memory, mb
1024
sql query :
SELECT * FROM sys.dm_os_performance_counters WHERE counter_name IN ('Target Server Memory (KB)','Total Server Memory (KB)','Logins/sec','Logouts/sec')
Resault:
object_name // counter_name// instance_name // cntr_value// cntr_type
SQLServer:General Statistics // Logins/sec // 1197 // 272696576
SQLServer:General Statistics // Logouts/sec // 1184 // 272696576
SQLServer:Memory Manager // Target Server Memory (KB) // 8323072 // 65792
SQLServer:Memory Manager // Total Server Memory (KB) // 1048576 // 65792
sql query :
DBCC MEMORYSTATUS
Resault:
Memory ManagerKB
VM Reserved1035824
VM Committed 52384
AWE Allocated 1048576
Reserved Memory1024
Reserved Memory In Use0
Thank you
March 2, 2012 at 10:06 am
Is Physical Address Extension (PAE) enabled?
March 2, 2012 at 10:13 am
PAE ? In file boot.ini ? Yes, Enabled!
March 4, 2012 at 10:15 pm
someone has ideas how to solve this problem
March 4, 2012 at 11:01 pm
you can obtain this information simply by querying the sys.dm_os_process_memory DMV. The physical_memory_in_use column indicates total memory usage
March 5, 2012 at 12:51 am
March 5, 2012 at 5:11 am
alexanko (3/2/2012)
Settings:
SQL:
Enable AWE Memory for SQL Server
Maximum server memory (in MB) - 11 000 Mb
Minimum server memory (in MB) - 11 000 Mb
I assume you have correctly set the PAE parameter in boot.ini & you have enabled AWE in SQL Server configuration.
Any particular reason to set MAX & MIN memory to same values? It will not allow SQL Server to adjust memory dynamically as per the memory pressure.
It should be mentioned that even if you set 11000 as MIN memory, SQL Server will not start using(or allocating) it immediatly. It will only get there if there is so much memory requirement. Yes, once it reaches there it will stay there.
March 13, 2012 at 11:12 pm
this code is executed. Rebooted the server. The result is the same.
March 13, 2012 at 11:26 pm
okbangas (3/4/2012)
what is the result of the following statement?
exec master.dbo.xp_readerrorlog 0, 1, 'Using Locked Pages For Buffer Pool';
...this code is executed
manukumar (3/4/2012)
you can obtain this information simply by querying the sys.dm_os_process_memory DMV. The physical_memory_in_use column indicates total memory usage
physical_memory_in_use_kb867928
large_page_allocations_kb0
locked_page_allocations_kb794624
total_virtual_address_space_kb2097024
virtual_address_space_reserved_kb1162268
virtual_address_space_committed_kb881744
virtual_address_space_available_kb934756
page_fault_count315372
memory_utilization_percentage100
available_commit_limit_kb10415884
process_physical_memory_low0
process_virtual_memory_low0
March 19, 2012 at 7:07 am
A few things about AWE...
The memory allocated as show in Task Manager always relates to memory used beklow the 4GB line. It is not aware of any memory allocated above the 4GB line. You need to use the SQL Server AWE memory counters to see how much AWE memory is used.
When SQL Server starts, if AWE is enabled and you are asking for a maximum memory above 3GB, SQL Server will make one and only one atempt to get that memory. If it fails, then SQL Server will just use memory below the 4GB line.
If you have 12GB memory total on the box, then you will never have 11GB memory free. Therfore your AWE request will fail.
There are a lot of threads about how to use AWE memory in SQL Server Central. You should browse them to look for the solution to your problem.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 19, 2012 at 7:56 am
Setting max server memory to the minimum value can severely reduce SQL Server performance and even prevent it from starting.
http://msdn.microsoft.com/en-us/library/ms178067.aspx
with a server with 12Gb of memory is not not necessary to leave enough memory for the memory allocation of the server to do it's job effectively?
reduce Max to 10Gb memory and min to 3Gb ... 2Gb always available to server @ max SQL usage and plenty when @ min usage levels ...
Leaves enough memory to the server ...
my noob 2cents
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
March 19, 2012 at 1:10 pm
This may be a silly point but if you're running this on a 32-bit windows server isn't the max RAM available only going to be 4GB?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 19, 2012 at 1:38 pm
MyDoggieJessie (3/19/2012)
This may be a silly point but if you're running this on a 32-bit windows server isn't the max RAM available only going to be 4GB?
No, with AWE enabled it can use up to 32gb RAM on Windows Server 2003 Enterprise. See here.
Jared
CE - Microsoft
March 5, 2015 at 12:12 am
Hi
I am having the exact same issue.
Is there any other feedback on this?
Would installing SP1/2/3 for SQL 2008 R2 help?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply