patternsqlMinor
SQL Server 2005 SP3 Memory Errors when plenty of memory is seemingly available
Viewed 0 times
plentyavailablesql2005seeminglymemorysp3servererrorswhen
Problem
I have a SQL 2005 SP3 box installed on Windows 2003 R2 SP2. The box has 64GB of Memory with AWE enabled (and /PAE in the boot.ini) as well as lock pages in memory enabled and Max Server Memory set to 45GB. The server also has 32 CPUs.
We are seeing errors in the logs as follows:
Error: 701, Severity: 17, State: 123.
There is insufficient system memory to run this query.
Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 65536
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 43160332, committed (KB): 467644, memory utilization: 45%.
With the Virtual Reserve error I identified that this was generally a problem caused by the MemToLeave being by default 256MB so I started SQL server with -g 512 to double that and see whether the Virtual Reserve error goes away. Immediately within 24 hours the SQL Service Abended due to that change. So we are now running with the default again which logs errors but does not abend the SQL service.
If I look at the ring buffer (sys.dm_os_ring_buffers) I see messages such as:
```
EventTime Type Indicators Avail Phys Mem, Kb Avail VAS, Kb Avail Phys Mem, Mb Avail VAS, Mb Avail Phys Mem, Gb Avail VAS, Gb
2012-06-19 10:37:54.063 RESOURCE_MEMPHYSICAL_LOW 2 19251252 126712 18800 123 18 0
2012-06-19 10:37:54.063 RESOURCE_MEM_STEADY 1 19251244 126392 18800 123 18 0
2012-06-19 10:37:38.063 RESOURCE_MEM_STEADY 0 19252512 127752 18801 124 18 0
2012-06-19 10:37:38.063 RESOURCE_MEMPHYSICAL_HIGH 1 19252512 127752 18801 124 18 0
2012-06-19 10:37:37.063 RESOURCE_MEMPHYSICAL_LOW 2 19250852 126344 18799 123 18 0
2012-06-19 10:37:27.063 RESOURCE_MEMPHYSICAL_LOW 2 19251028 126600 18799 123 18 0
2012-06-19 10:37:27.063 RESOURCE_MEM_STEADY 0 19251284 126856 18800 123 18 0
2012-06-19 10:37:27.063 RESOURCE_MEMPHYSICAL_HIGH 1 19251284 126856 18800 123 18 0
2
We are seeing errors in the logs as follows:
Error: 701, Severity: 17, State: 123.
There is insufficient system memory to run this query.
Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 65536
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 43160332, committed (KB): 467644, memory utilization: 45%.
With the Virtual Reserve error I identified that this was generally a problem caused by the MemToLeave being by default 256MB so I started SQL server with -g 512 to double that and see whether the Virtual Reserve error goes away. Immediately within 24 hours the SQL Service Abended due to that change. So we are now running with the default again which logs errors but does not abend the SQL service.
If I look at the ring buffer (sys.dm_os_ring_buffers) I see messages such as:
```
EventTime Type Indicators Avail Phys Mem, Kb Avail VAS, Kb Avail Phys Mem, Mb Avail VAS, Mb Avail Phys Mem, Gb Avail VAS, Gb
2012-06-19 10:37:54.063 RESOURCE_MEMPHYSICAL_LOW 2 19251252 126712 18800 123 18 0
2012-06-19 10:37:54.063 RESOURCE_MEM_STEADY 1 19251244 126392 18800 123 18 0
2012-06-19 10:37:38.063 RESOURCE_MEM_STEADY 0 19252512 127752 18801 124 18 0
2012-06-19 10:37:38.063 RESOURCE_MEMPHYSICAL_HIGH 1 19252512 127752 18801 124 18 0
2012-06-19 10:37:37.063 RESOURCE_MEMPHYSICAL_LOW 2 19250852 126344 18799 123 18 0
2012-06-19 10:37:27.063 RESOURCE_MEMPHYSICAL_LOW 2 19251028 126600 18799 123 18 0
2012-06-19 10:37:27.063 RESOURCE_MEM_STEADY 0 19251284 126856 18800 123 18 0
2012-06-19 10:37:27.063 RESOURCE_MEMPHYSICAL_HIGH 1 19251284 126856 18800 123 18 0
2
Solution
The AWE mechanism in 32 bit process can only be used for data pages (buffer pool). It cannot be used for procedure cache, for query memory grants, for execution stacks, for access token cache, for CLR etc etc etc, basically all the other allocations other than data pages. All these allocations (including code pages) have to cram in the 2GB of the process address space.
Your only solution worth considering is moving to a 64bit SQL Server deployment on a 64bit OS. Everything else is a waste of time.
See Using AWE
The SQL Server buffer pool can fully utilize AWE mapped memory;
however, only database pages can be dynamically mapped to and unmapped
from SQL Server's virtual address space and take full advantage of
memory allocated through AWE. AWE does not directly help supporting
additional users, threads, databases, queries, and other objects that
permanently reside in the virtual address space.
Your only solution worth considering is moving to a 64bit SQL Server deployment on a 64bit OS. Everything else is a waste of time.
See Using AWE
The SQL Server buffer pool can fully utilize AWE mapped memory;
however, only database pages can be dynamically mapped to and unmapped
from SQL Server's virtual address space and take full advantage of
memory allocated through AWE. AWE does not directly help supporting
additional users, threads, databases, queries, and other objects that
permanently reside in the virtual address space.
Context
StackExchange Database Administrators Q#19488, answer score: 6
Revisions (0)
No revisions yet.