FIX: Out of memory error when the virtual address space of the SQL Server process is very low on available memory

Notice
After you apply this update, you have to add the trace flag -T8075 as a startup parameter to enable this change.
Symptoms
When you run a query in a 64-bit version of Microsoft SQL Server 2014 or Microsoft SQL Server 2012, you receive an out-of-memory error message that resembles the following in the SQL Server error log:

Failed allocate pages: FAIL_PAGE_ALLOCATION 513

Queries take a long time to finish execution and encounter SOS_MEMORY_TOPLEVELBLOCKALLOCATOR waits.

When you examine the following information points, you will find that there is very low available virtual address space:

  • DBCC MEMORYSTATUS - Process/System Counts section - Available Virtual Memory
  • DMV: sys.dm_os_process_memory - column virtual_address_space_available_kb
These values start off around 8 terabytes (TB) on an x64 process and continue to climb down and reach a few gigabytes (GB). 

When you're at the stage where the available virtual address space is very low, queries that try to perform memory allocation may also encounter a wait type of CMEMTHREAD.

The following data points will continue to increase over time:
  • DMV: sys.dm_os_process_memory and sys.dm_os_memory_nodes - column virtual_address_space_reserved_kb
  • DBCC MEMORYSTATUS - Memory Manager section - VM Reserved

These values will typically increase in multiples of the "max server memory" value up to almost 8 TB.
Cause
When the SQL Server process has reached the state where Total Server Memory = Target Server Memory = max server memory, there are policies in SQL Server memory manager to let new allocations request multiple 8 KB pages to succeed temporarily. Repeated allocation pattern under such condition may cause fragmentation of the memory blocks and consumption of virtual address space. If this process repeats many times, the SQL Server virtual address space will be exhausted, and you will notice the symptoms that were mentioned earlier.
Resolution

Hotfix information

To resolve this issue, install the hotfix from FIX: Error when your stored procedure calls another stored procedure on linked server in SQL Server 2014.

Prerequisites

You must have Cumulative Update 3 for SQL Server 2014 Service Pack 1 installed to apply this hotfix.

Notice

After you apply this update, you have to add the trace flag -T8075 as a startup parameter to enable this change.  

Cumulative update information

The issue was first fixed in the following cumulative update of SQL Server.

Recommendation: Install the latest cumulative update for SQL Server
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. We recommend that you download and install the latest cumulative updates for SQL Server:


This hotfix prevents both the out of memory and the continuous reduction of available virtual address space you may experience.
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
More information
  • Windows 2012 R2 allows virtual address space to grow as large as 128 TB. Therefore, you may not notice this issue in Windows 2012 R2 environments. For more information, see the following topic in the Windows Dev Center:

    Memory limits for Windows and Windows Server releases
  • If you see continuous growth in virtual address space even after you apply the fix, you can determine which queries or operations are requesting large chunks of memory by using the Page_allocated extended event. A sample script looks like this:

    CREATE EVENT SESSION [memory_tracking] ON SERVERADD EVENT sqlos.page_allocated(    ACTION(package0.callstack,sqlos.cpu_id,sqlos.task_address,sqlos.worker_address,sqlserver.database_id,sqlserver.query_hash,sqlserver.request_id,sqlserver.session_id,sqlserver.sql_text)    WHERE ([number_pages]>(1)))ADD TARGET package0.event_file(SET filename=N'E:\Data\MSSQL11.MSSQLSERVER\MSSQL\Log\memory_tracking.xel')WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)GO
    Typically these are log backups and index maintenance operations, which occur frequently.
Свойства

ИД на статията: 3074434 – Последен преглед: 10/10/2016 01:59:00 – Редакция: 11.0

Microsoft SQL Server 2012 Service Pack 2, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Web, Microsoft SQL Server 2014 Standard, Microsoft SQL Server 2014 Service Pack 1

  • kbqfe kbsurveynew kbexpertiseadvanced kbfix KB3074434
Обратна връзка