Sql-server – SQL Server 2005 SP3 Memory Errors when plenty of memory is seemingly available

sql serversql-server-2005

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
2012-06-19 10:37:23.063 RESOURCE_MEMPHYSICAL_LOW    2   19251208    126792  18800   123 18  0
2012-06-19 10:37:23.063 RESOURCE_MEM_STEADY 0   19251192    126728  18799   123 18  0
2012-06-19 10:37:23.063 RESOURCE_MEMPHYSICAL_HIGH   1   19251192    126728  18799   123 18  0

Being logged in the ring buffers. These messages are being placed on the ring buffer almost every second.

If I run the memory queries found at http://blogs.msdn.com/b/sqlsakthi/archive/2011/02/28/t-sql-script-to-monitor-memory-usage-by-sql-server-instance.aspx I get the following results:

----------------------------------------------------------------------------------------------------
Memory usage details for SQL Server instance HOFSQL1AP (9.00.4053.00 - X86) - Enterprise Edition)
----------------------------------------------------------------------------------------------------

--------------------------------------
Memory visible to the Operating System

Physical Memory_MB                      Physical Memory_GB                      Virtual Memory GB
--------------------------------------- --------------------------------------- ---------------------------------------
65536                                   64                                      2


-------------------------------
Buffer Pool Usage at the Moment

BPool_Committed_MB                      BPool_Commit_Tgt_MB                     BPool_Visible_MB
--------------------------------------- --------------------------------------- ---------------------------------------
45056.000000                            45056.000000                            920.000000


---------------------------------------------------------------------------
Total Memory used by SQL Server Buffer Pool as reported by Perfmon counters

Mem_KB               Mem_MB                                  Mem_GB
-------------------- --------------------------------------- ---------------------------    ------------
46137344             45056.000000                            44.000000000


-------------------------------------------------------------
Memory needed as per current Workload for SQL Server instance

Mem_KB               Mem_MB                                  Mem_GB
-------------------- --------------------------------------- ---------------------------------------
46137344             45056.000000                            44.000000000


------------------------------------------------------------------------------
Total amount of dynamic memory the server is using for maintaining connections

Mem_KB               Mem_MB                                  Mem_GB
-------------------- --------------------------------------- ---------------------------------------
16128                15.750000                               0.015380859


------------------------------------------------------------
Total amount of dynamic memory the server is using for locks

Mem_KB               Mem_MB                                  Mem_GB
-------------------- --------------------------------------- ---------------------------------------
11584                11.312500                               0.011047363


----------------------------------------------------------------------------
Total amount of dynamic memory the server is using for the dynamic SQL cache

Mem_KB               Mem_MB                                  Mem_GB
-------------------- --------------------------------------- ---------------------------------------
4592                 4.484375                                0.004379272


-------------------------------------------------------------------------
Total amount of dynamic memory the server is using for query optimization

Mem_KB               Mem_MB                                  Mem_GB
-------------------- --------------------------------------- ---------------------------------------
6608                 6.453125                                0.006301879


-------------------------------------------------------------------------------
Total amount of dynamic memory used for hash, sort and create index operations.

Mem_KB               Mem_MB                                  Mem_GB
-------------------- --------------------------------------- ---------------------------------------
119584               116.781250                              0.114044189


------------------------------------------
Total Amount of memory consumed by cursors

Mem_KB               Mem_MB                                  Mem_GB
-------------------- --------------------------------------- ---------------------------------------
2989                 2.918945                                0.002850532


-------------------------------------------------------------------------
Number of pages in the buffer pool (includes database, free, and stolen).

8KB_Pages            Pages_in_KB                             Pages_in_MB
-------------------- --------------------------------------- ---------------------------------------
5767168              46137344.000000                         45056.000000000


---------------------------------------
Number of Data pages in the buffer pool

8KB_Pages            Pages_in_KB                             Pages_in_MB
-------------------- --------------------------------------- ---------------------------------------
5700255              45602040.000000                         44533.242187500


---------------------------------------
Number of Free pages in the buffer pool

8KB_Pages            Pages_in_KB                             Pages_in_MB
-------------------- --------------------------------------- ---------------------------------------
8046                 64368.000000                            62.859375000


-------------------------------------------
Number of Reserved pages in the buffer pool

8KB_Pages            Pages_in_KB                             Pages_in_MB
-------------------- --------------------------------------- ---------------------------------------
14311                114488.000000                           111.804687500


-----------------------------------------
Number of Stolen pages in the buffer pool

8KB_Pages            Pages_in_KB                             Pages_in_MB
-------------------- --------------------------------------- ---------------------------------------
58867                470936.000000                           459.898437500


---------------------------------------------
Number of Plan Cache pages in the buffer pool

8KB_Pages            Pages_in_KB                             Pages_in_MB
-------------------- --------------------------------------- ---------------------------------------
24712                197696.000000                           193.062500000


-----------------------------------------------------------------------------------------------
Page Life Expectancy - Number of seconds a page will stay in the buffer pool without references

Page Life in seconds PLE Status
-------------------- ------------------
1054                 PLE is Healthy


--------------------------------------------------------------
Number of requests per second that had to wait for a free page

Free list stalls/sec
--------------------
506909


-----------------------------------------------------------------------------------------------------------------
Number of pages flushed to disk/sec by a checkpoint or other operation that require all dirty pages to be flushed

Checkpoint pages/sec
--------------------
27495457


------------------------------------------------------------------------
Number of buffers written per second by the buffer manager"s lazy writer

Lazy writes/sec
--------------------
4862949


--------------------------------------------------------------
Total number of processes waiting for a workspace memory grant

Memory Grants Pending
---------------------
0


----------------------------------------------------------------------------------
Total number of processes that have successfully acquired a workspace memory grant

Memory Grants Outstanding
-------------------------
7

I have tried diagnosing this issue and come up against a brick wall. The only thing I can think of now is to put out a request to the community to see if anyone has any tips as to how I can resolve these memory bottlenecks.

BTW. I originally gave 10GB to the OS and 54GB to the SQL Buffer pool. But when these errors started showing up I thought that the OS was memory starved (because our infrastructure guys use Tivoli agents for monitoring and backups which get installed on the box), so I increased the OS allocation by decreasing the allocation to SQL.

Thanks

Jonathan Russell

Best Answer

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.

Related Question