4

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 Link 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

Improve this question

2 Answers 2

Reset to default
6

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.

Improve this answer
2
  • This piece of information about AWE is new to me. Is there an msdn article with more details about shortcomings of AWE for SQL Server? Thanks!
    – RK Kuppala
    Commented Jun 19, 2012 at 11:50
  • @yogirk: see my update
    – Remus Rusanu
    Commented Jun 19, 2012 at 12:11
6

I agree with @RemusRusanu, a move to 64bit is pretty much guaranteed to take the pain away.

As that move to 64bit might not be something you can JFDI tomorrow, I'd be inclined to invest a little more time investigating while planning the upgrade. You might be able to get your head above water by alleviating the issue temporarily.

Good reference post for virtual address exhaustion on 32 bit is How to find who is using/eating up the Virtual Address space on your SQL Server. In particular pay attention to the list of components that require allocations outside the buffer pool. I'd also eyeball the 3rd party monitoring/backup components you mentioned.

  1. Any OLE/COM components loaded in SQL Server.
  2. Extended Stored Procedures (use sys.dm_os_loaded_modules to identify the module loaded in sqlserver process space).
  3. SQL Mail..
  4. Prepared documents using sp_xml_preparedocument.
  5. Linked Server Providers.
  6. Large Plans stored in Procedure Cache.
  7. Very frequent Backups also may cause MTL area depletion.
  8. SQL CLR.

If you have an edge feature (something non-core/rarely used) that falls into the categories above then you can make a case for disabling or removing it until you move to 64bit.

Often, 80% of features at 99.99% availability is more important than 100% of features at 90% availability.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.